-- Create the database
CREATE DATABASE IF NOT EXISTS neuwegdb;
USE neuwegdb;

-- Create admin_users table
CREATE TABLE IF NOT EXISTS admin_users (
id VARCHAR(36) PRIMARY KEY,
name VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL UNIQUE,
password VARCHAR(255) NOT NULL,
role ENUM('admin', 'manager', 'user') NOT NULL DEFAULT 'user',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Create web_accounts table
CREATE TABLE IF NOT EXISTS web_accounts (
id VARCHAR(36) PRIMARY KEY,
name VARCHAR(255) NOT NULL,
url VARCHAR(255) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Create messages table with updated fields
CREATE TABLE IF NOT EXISTS messages (
id VARCHAR(36) PRIMARY KEY,
account_id VARCHAR(36) NOT NULL,
sender_first_name VARCHAR(100) NOT NULL,
sender_last_name VARCHAR(100) NOT NULL,
sender_email VARCHAR(255) NOT NULL,
sender_phone VARCHAR(50),
content TEXT NOT NULL,
company_name VARCHAR(255),
status ENUM('read', 'unread') NOT NULL DEFAULT 'unread',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (account_id) REFERENCES web_accounts(id) ON DELETE CASCADE
);

-- Create user_settings table
CREATE TABLE IF NOT EXISTS user_settings (
user_id VARCHAR(36) PRIMARY KEY,
email_notifications BOOLEAN NOT NULL DEFAULT TRUE,
new_message_alert BOOLEAN NOT NULL DEFAULT TRUE,
account_activity_alert BOOLEAN NOT NULL DEFAULT FALSE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES admin_users(id) ON DELETE CASCADE
);

-- Insert sample data
-- Admin user (email: admin@admin.com, password: 123456)
INSERT INTO admin_users (id, name, email, password, role)
VALUES (UUID(), 'Admin User', 'admin@admin.com', '123456', 'admin');

-- Sample web accounts
INSERT INTO web_accounts (id, name, url)
VALUES 
(UUID(), 'Company Website', 'https://example.com'),
(UUID(), 'E-commerce Store', 'https://store.example.com'),
(UUID(), 'Support Portal', 'https://support.example.com');

-- Get the IDs of the inserted accounts
SET @company_id = (SELECT id FROM web_accounts WHERE name = 'Company Website' LIMIT 1);
SET @store_id = (SELECT id FROM web_accounts WHERE name = 'E-commerce Store' LIMIT 1);
SET @support_id = (SELECT id FROM web_accounts WHERE name = 'Support Portal' LIMIT 1);

-- Sample messages with updated fields
INSERT INTO messages (id, account_id, sender_first_name, sender_last_name, sender_email, sender_phone, content, company_name, status, created_at)
VALUES
(UUID(), @company_id, 'John', 'Doe', 'john@example.com', '+1234567890', 'I would like to inquire about your services. Can you provide more information?', 'ABC Corp', 'unread', NOW() - INTERVAL 1 DAY),
(UUID(), @company_id, 'Jane', 'Smith', 'jane@example.com', '+1987654321', 'Hello, I found your website and I am interested in your products. Please contact me.', 'XYZ Inc', 'read', NOW() - INTERVAL 3 DAY),
(UUID(), @store_id, 'Mike', 'Johnson', 'mike@example.com', '+1122334455', 'I placed an order yesterday but haven\'t received a confirmation email. My order number is #12345.', 'Johnson LLC', 'unread', NOW() - INTERVAL 12 HOUR),
(UUID(), @store_id, 'Sarah', 'Williams', 'sarah@example.com', '+1555666777', 'Do you ship internationally? I\'m interested in purchasing several items but I live overseas.', 'Global Imports', 'read', NOW() - INTERVAL 5 DAY),
(UUID(), @support_id, 'David', 'Brown', 'david@example.com', '+1999888777', 'I\'m having trouble logging into my account. I\'ve tried resetting my password but I\'m not receiving the reset email.', 'Tech Solutions', 'unread', NOW() - INTERVAL 6 HOUR),
(UUID(), @support_id, 'Lisa', 'Garcia', 'lisa@example.com', '+1444333222', 'Thank you for your quick response to my previous inquiry. The issue has been resolved.', 'Garcia Enterprises', 'read', NOW() - INTERVAL 2 DAY);

