SET NAMES utf8mb4;
CREATE TABLE companies(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(150) NOT NULL,timezone VARCHAR(64) NOT NULL DEFAULT 'Africa/Johannesburg',created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE users(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id BIGINT UNSIGNED NOT NULL,name VARCHAR(150) NOT NULL,email VARCHAR(190) NOT NULL,password_hash VARCHAR(255) NOT NULL,role ENUM('admin','manager','salesperson','viewer') NOT NULL DEFAULT 'salesperson',active TINYINT(1) DEFAULT 1,last_login_at DATETIME NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,UNIQUE KEY(email),CONSTRAINT users_company_fk FOREIGN KEY(company_id) REFERENCES companies(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE leads(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id BIGINT UNSIGNED NOT NULL,owner_id BIGINT UNSIGNED NULL,company_name VARCHAR(180) NOT NULL,contact_name VARCHAR(160) NOT NULL,email VARCHAR(190) NULL,phone VARCHAR(40) NULL,service VARCHAR(160) NOT NULL,source VARCHAR(100) NULL,deal_value DECIMAL(14,2) NOT NULL DEFAULT 0,probability TINYINT UNSIGNED NOT NULL DEFAULT 20,status ENUM('New','Contacted','Qualified','Pitch prepared','Pitch sent','Meeting scheduled','Quotation requested','Quotation sent','Negotiation','Awaiting decision','Won','Lost','On hold') NOT NULL DEFAULT 'New',priority ENUM('Low','Medium','High') NOT NULL DEFAULT 'Medium',next_follow_up DATETIME NULL,last_contact_at DATETIME NULL,expected_close_date DATE NULL,notes TEXT NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,deleted_at DATETIME NULL,KEY leads_company_status(company_id,status),CONSTRAINT leads_company_fk FOREIGN KEY(company_id) REFERENCES companies(id),CONSTRAINT leads_owner_fk FOREIGN KEY(owner_id) REFERENCES users(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE tasks(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id BIGINT UNSIGNED NOT NULL,lead_id BIGINT UNSIGNED NULL,assigned_user_id BIGINT UNSIGNED NOT NULL,title VARCHAR(190) NOT NULL,type ENUM('call','email','meeting','quotation','payment','proposal','deadline','personal') NOT NULL DEFAULT 'personal',due_at DATETIME NOT NULL,priority ENUM('Low','Medium','High') NOT NULL DEFAULT 'Medium',status ENUM('Pending','Completed','Rescheduled') NOT NULL DEFAULT 'Pending',notes TEXT NULL,completed_at DATETIME NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,KEY tasks_due(company_id,status,due_at),CONSTRAINT tasks_company_fk FOREIGN KEY(company_id) REFERENCES companies(id),CONSTRAINT tasks_lead_fk FOREIGN KEY(lead_id) REFERENCES leads(id),CONSTRAINT tasks_user_fk FOREIGN KEY(assigned_user_id) REFERENCES users(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE lead_activities(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id BIGINT UNSIGNED NOT NULL,lead_id BIGINT UNSIGNED NOT NULL,user_id BIGINT UNSIGNED NULL,type VARCHAR(60) NOT NULL,summary VARCHAR(255) NOT NULL,details TEXT NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,KEY activity_lead(lead_id,created_at),CONSTRAINT activity_company_fk FOREIGN KEY(company_id) REFERENCES companies(id),CONSTRAINT activity_lead_fk FOREIGN KEY(lead_id) REFERENCES leads(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE notes(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id BIGINT UNSIGNED NOT NULL,user_id BIGINT UNSIGNED NOT NULL,lead_id BIGINT UNSIGNED NULL,title VARCHAR(190) NOT NULL,body MEDIUMTEXT NULL,tags VARCHAR(255) NULL,pinned TINYINT(1) DEFAULT 0,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,CONSTRAINT notes_company_fk FOREIGN KEY(company_id) REFERENCES companies(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE scheduled_emails(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id BIGINT UNSIGNED NOT NULL,user_id BIGINT UNSIGNED NOT NULL,lead_id BIGINT UNSIGNED NULL,recipient_email VARCHAR(190) NOT NULL,subject VARCHAR(220) NOT NULL,body MEDIUMTEXT NOT NULL,scheduled_at DATETIME NULL,status ENUM('Draft','Scheduled','Processing','Sent','Failed','Cancelled') NOT NULL DEFAULT 'Draft',attempts TINYINT UNSIGNED NOT NULL DEFAULT 0,last_error VARCHAR(500) NULL,sent_at DATETIME NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,KEY emails_schedule(company_id,status,scheduled_at),CONSTRAINT emails_company_fk FOREIGN KEY(company_id) REFERENCES companies(id),CONSTRAINT emails_user_fk FOREIGN KEY(user_id) REFERENCES users(id),CONSTRAINT emails_lead_fk FOREIGN KEY(lead_id) REFERENCES leads(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE pitches(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id BIGINT UNSIGNED NOT NULL,user_id BIGINT UNSIGNED NOT NULL,lead_id BIGINT UNSIGNED NULL,title VARCHAR(190) NOT NULL,format ENUM('Email pitch','WhatsApp pitch','Phone script','Meeting pitch','Formal proposal','Elevator pitch','Follow-up message','LinkedIn outreach') NOT NULL DEFAULT 'Email pitch',client_problem TEXT NULL,proposed_solution TEXT NULL,benefits TEXT NULL,pricing VARCHAR(190) NULL,content MEDIUMTEXT NOT NULL,status ENUM('Draft','Ready','Sent') NOT NULL DEFAULT 'Draft',created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,CONSTRAINT pitches_company_fk FOREIGN KEY(company_id) REFERENCES companies(id),CONSTRAINT pitches_user_fk FOREIGN KEY(user_id) REFERENCES users(id),CONSTRAINT pitches_lead_fk FOREIGN KEY(lead_id) REFERENCES leads(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE audit_logs(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id BIGINT UNSIGNED NULL,user_id BIGINT UNSIGNED NULL,action VARCHAR(120) NOT NULL,context JSON NULL,ip_address VARCHAR(45) NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,KEY audit_company_created(company_id,created_at)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE system_settings(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id BIGINT UNSIGNED NOT NULL,setting_key VARCHAR(100) NOT NULL,setting_value MEDIUMTEXT NULL,encrypted TINYINT(1) NOT NULL DEFAULT 0,updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,UNIQUE KEY settings_company_key(company_id,setting_key),CONSTRAINT settings_company_fk FOREIGN KEY(company_id) REFERENCES companies(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;