CREATE TABLE IF NOT EXISTS users (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(120) NOT NULL,
email VARCHAR(190) NOT NULL UNIQUE,
password VARCHAR(255) NOT NULL,
role ENUM('Admin','Manager','Sales','Staff') NOT NULL DEFAULT 'Staff',
active TINYINT(1) NOT NULL DEFAULT 1,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS leads (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
company_name VARCHAR(180) NOT NULL,
contact_name VARCHAR(120) NULL,
phone VARCHAR(50) NULL,
email VARCHAR(190) NULL,
service VARCHAR(150) NULL,
source VARCHAR(100) NULL,
status ENUM('New','Contacted','Qualified','Proposal','Won','Lost') NOT NULL DEFAULT 'New',
assigned_to BIGINT UNSIGNED NULL,
next_follow_up DATE NULL,
notes TEXT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX idx_leads_status(status),
INDEX idx_leads_assigned(assigned_to),
CONSTRAINT fk_leads_user
FOREIGN KEY (assigned_to) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS clients (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
client_code VARCHAR(40) NOT NULL UNIQUE,
company_name VARCHAR(180) NOT NULL,
contact_name VARCHAR(120) NULL,
phone VARCHAR(50) NULL,
email VARCHAR(190) NULL,
address VARCHAR(255) NULL,
service VARCHAR(150) NULL,
account_manager BIGINT UNSIGNED NULL,
notes TEXT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX idx_clients_manager(account_manager),
CONSTRAINT fk_clients_manager
FOREIGN KEY (account_manager) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS projects (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
client_id BIGINT UNSIGNED NOT NULL,
name VARCHAR(180) NOT NULL,
service VARCHAR(150) NULL,
description TEXT NULL,
start_date DATE NULL,
end_date DATE NULL,
priority ENUM('Low','Medium','High','Urgent') NOT NULL DEFAULT 'Medium',
status ENUM('Planned','In Progress','On Hold','Completed','Cancelled') NOT NULL DEFAULT 'Planned',
assigned_to BIGINT UNSIGNED NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX idx_projects_client(client_id),
INDEX idx_projects_status(status),
INDEX idx_projects_assigned(assigned_to),
CONSTRAINT fk_projects_client
FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
CONSTRAINT fk_projects_user
FOREIGN KEY (assigned_to) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS invoices (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
client_id BIGINT UNSIGNED NOT NULL,
invoice_number VARCHAR(50) NOT NULL UNIQUE,
subtotal DECIMAL(12,2) NOT NULL DEFAULT 0,
discount DECIMAL(12,2) NOT NULL DEFAULT 0,
tax DECIMAL(12,2) NOT NULL DEFAULT 0,
total_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
paid_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
due_date DATE NOT NULL,
status ENUM('Draft','Pending','Partially Paid','Paid','Overdue','Cancelled') NOT NULL DEFAULT 'Pending',
notes TEXT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX idx_invoices_client(client_id),
INDEX idx_invoices_status(status),
INDEX idx_invoices_due(due_date),
CONSTRAINT fk_invoices_client
FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS payments (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
invoice_id BIGINT UNSIGNED NOT NULL,
amount DECIMAL(12,2) NOT NULL,
payment_date DATE NOT NULL,
payment_method ENUM('Cash','Bank Transfer','Card','ABA','ACLEDA','Wing','Other') NOT NULL DEFAULT 'Bank Transfer',
reference VARCHAR(120) NULL,
notes TEXT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX idx_payments_invoice(invoice_id),
CONSTRAINT fk_payments_invoice
FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS activity_logs (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
user_id BIGINT UNSIGNED NULL,
action VARCHAR(80) NOT NULL,
entity_type VARCHAR(50) NOT NULL,
entity_id BIGINT UNSIGNED NULL,
details TEXT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX idx_activity_created(created_at),
CONSTRAINT fk_activity_user
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;