-- ============================================================
--  Target Maids CRM — Final Schema Setup
--  Matches your EXISTING users table (id, username, password_hash,
--  role, created_at) by extending it, rather than replacing it.
-- ============================================================

-- 1) Extend the existing users table with the columns the CRM needs
ALTER TABLE users
  ADD COLUMN full_name VARCHAR(100) NULL AFTER username,
  ADD COLUMN manager_id INT UNSIGNED NULL AFTER role,
  ADD COLUMN active TINYINT(1) NOT NULL DEFAULT 1 AFTER manager_id,
  MODIFY COLUMN role ENUM('user','admin','manager','sales','reception','accounts') NOT NULL DEFAULT 'user',
  ADD CONSTRAINT fk_users_manager FOREIGN KEY (manager_id) REFERENCES users(id) ON DELETE SET NULL;

-- 2) Seed a CRM admin login — username: tmadmin / password: ChangeMe123!
--    CHANGE THIS PASSWORD after your first login.
INSERT INTO users (username, full_name, password_hash, role, active)
VALUES ('tmadmin', 'Target Maids Admin', '$2b$10$6HgTZ7dUXBfD0Ex3DWY7XOXz.V3g0LK1wRBbAkqPqBDqRjcsthn96', 'admin', 1);

CREATE TABLE leads (
    id INT AUTO_INCREMENT PRIMARY KEY,
    lead_unique_id VARCHAR(30) NOT NULL UNIQUE,   -- e.g. TM_0001
    name VARCHAR(150),
    mobile VARCHAR(20) NOT NULL,
    nationality VARCHAR(50),
    requirement TEXT,                     -- e.g. "Live-in housemaid, Filipino, cooking experience"
    source ENUM('whatsapp','reception','manual','other') DEFAULT 'manual',
    status ENUM('New','Contacted','Interested','Follow Up','Closed Won','Closed Lost') DEFAULT 'New',
    assigned_to INT UNSIGNED NULL,                 -- sales agent (users.id)
    created_by INT UNSIGNED NULL,                  -- reception/admin who added it manually
    closed_by INT UNSIGNED NULL,
    closed_at DATETIME NULL,
    follow_up_date DATETIME NULL,
    notes TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (assigned_to) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (closed_by) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE housemaids (
    id INT AUTO_INCREMENT PRIMARY KEY,
    profile_unique_id VARCHAR(30) NOT NULL UNIQUE,  -- e.g. HM_0001
    full_name VARCHAR(150),
    photo_url VARCHAR(255),
    nationality VARCHAR(50),
    date_of_birth DATE,
    passport_number VARCHAR(50),
    passport_expiry DATE,
    religion VARCHAR(50),
    marital_status VARCHAR(30),
    years_experience INT,
    skills TEXT,                          -- cooking, childcare, elderly care, driving, etc (comma or JSON)
    languages VARCHAR(150),
    salary_expectation DECIMAL(10,2),
    availability_status ENUM('Available','Reserved','Placed','Unavailable') DEFAULT 'Available',
    height_cm INT,
    weight_kg INT,
    documents JSON,                       -- {passport: url, medical: url, cv: url, ...}
    notes TEXT,
    created_by INT UNSIGNED NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
);

-- Which housemaid(s) a lead is interested in / matched with
CREATE TABLE lead_housemaid_matches (
    id INT AUTO_INCREMENT PRIMARY KEY,
    lead_id INT NOT NULL,
    housemaid_id INT NOT NULL,
    status ENUM('Proposed','Interviewing','Accepted','Rejected') DEFAULT 'Proposed',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (lead_id) REFERENCES leads(id) ON DELETE CASCADE,
    FOREIGN KEY (housemaid_id) REFERENCES housemaids(id) ON DELETE CASCADE
);

-- Closed-deal records that Accounts manages (payments, contracts)
CREATE TABLE deals (
    id INT AUTO_INCREMENT PRIMARY KEY,
    lead_id INT NOT NULL,
    housemaid_id INT NULL,
    sales_agent_id INT UNSIGNED NOT NULL,
    deal_amount DECIMAL(10,2) NOT NULL,
    amount_paid DECIMAL(10,2) DEFAULT 0,
    payment_status ENUM('Unpaid','Partial','Paid') DEFAULT 'Unpaid',
    contract_url VARCHAR(255),
    closed_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    notes TEXT,
    FOREIGN KEY (lead_id) REFERENCES leads(id) ON DELETE CASCADE,
    FOREIGN KEY (housemaid_id) REFERENCES housemaids(id) ON DELETE SET NULL,
    FOREIGN KEY (sales_agent_id) REFERENCES users(id) ON DELETE RESTRICT
);

CREATE TABLE whatsapp_messages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    cloudwa_id VARCHAR(100) NULL UNIQUE,   -- dedupe key (bridge's wa_message_id)
    lead_id INT NOT NULL,
    from_number VARCHAR(20),
    to_number VARCHAR(20),
    direction ENUM('inbound','outbound') NOT NULL,
    message TEXT,
    media_url VARCHAR(255),
    media_type VARCHAR(50),
    media_filename VARCHAR(150),
    message_id VARCHAR(100),
    sent_at DATETIME,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (lead_id) REFERENCES leads(id) ON DELETE CASCADE
);

-- Round-robin bookkeeping: who got the last lead
CREATE TABLE round_robin_state (
    id INT PRIMARY KEY DEFAULT 1,
    last_assigned_user_id INT UNSIGNED NULL
);
INSERT INTO round_robin_state (id, last_assigned_user_id) VALUES (1, NULL);

-- ── Marketing / Bulk messaging ──────────────────────────────
CREATE TABLE broadcast_templates (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    type ENUM('text','image') DEFAULT 'text',
    message_text TEXT,
    image_url VARCHAR(255),
    created_by INT UNSIGNED NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE broadcast_campaigns (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    template_id INT NOT NULL,
    delay_seconds INT DEFAULT 5,          -- gap between each message in the campaign
    status ENUM('draft','scheduled','running','completed','cancelled') DEFAULT 'draft',
    scheduled_at DATETIME NULL,
    started_at DATETIME NULL,
    completed_at DATETIME NULL,
    created_by INT UNSIGNED NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (template_id) REFERENCES broadcast_templates(id) ON DELETE RESTRICT,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE broadcast_recipients (
    id INT AUTO_INCREMENT PRIMARY KEY,
    campaign_id INT NOT NULL,
    lead_id INT NULL,
    phone VARCHAR(20) NOT NULL,
    status ENUM('pending','sent','failed') DEFAULT 'pending',
    sent_at DATETIME NULL,
    error_message VARCHAR(255) NULL,
    FOREIGN KEY (campaign_id) REFERENCES broadcast_campaigns(id) ON DELETE CASCADE,
    FOREIGN KEY (lead_id) REFERENCES leads(id) ON DELETE SET NULL
);

CREATE TABLE activity_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NULL,
    action VARCHAR(100),
    details TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);