-- support.msuit.in — multi-tenant helpdesk schema
-- DB: support_msuit

CREATE TABLE IF NOT EXISTS support_tenants (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    slug VARCHAR(80) NOT NULL UNIQUE,          -- used in registration links: ?t=slug
    contact_email VARCHAR(150) NOT NULL,
    ticket_prefix VARCHAR(10) NOT NULL DEFAULT 'TCK',
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Products a tenant supports (Dental, Pharmacy, WhatsApp CRM, MidasInfraSuite...)
CREATE TABLE IF NOT EXISTS support_products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    sort_order INT NOT NULL DEFAULT 0,
    FOREIGN KEY (tenant_id) REFERENCES support_tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Categories (Technical, Billing, Sales, Marketing...) — addable per tenant
CREATE TABLE IF NOT EXISTS support_categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    sort_order INT NOT NULL DEFAULT 0,
    FOREIGN KEY (tenant_id) REFERENCES support_tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Your support staff
CREATE TABLE IF NOT EXISTS support_agents (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    name VARCHAR(150) NOT NULL,
    email VARCHAR(150) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('owner','agent') NOT NULL DEFAULT 'agent',
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_tenant_email (tenant_id, email),
    FOREIGN KEY (tenant_id) REFERENCES support_tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Clients who raise tickets
CREATE TABLE IF NOT EXISTS support_customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    name VARCHAR(150) NOT NULL,
    email VARCHAR(150) NOT NULL,
    phone VARCHAR(30) DEFAULT NULL,
    company_name VARCHAR(150) DEFAULT NULL,
    password_hash VARCHAR(255) NOT NULL,
    must_reset_password TINYINT(1) NOT NULL DEFAULT 1,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_tenant_cust_email (tenant_id, email),
    FOREIGN KEY (tenant_id) REFERENCES support_tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS support_tickets (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    ticket_number VARCHAR(30) NOT NULL,
    customer_id INT NOT NULL,
    product_id INT NOT NULL,
    category_id INT NOT NULL,
    priority ENUM('High','Medium','Low') NOT NULL DEFAULT 'Medium',
    status ENUM('Open','Assigned','In Progress','Pending','Resolved','Closed') NOT NULL DEFAULT 'Open',
    subject VARCHAR(255) NOT NULL,
    body MEDIUMTEXT NOT NULL,
    assigned_agent_id INT DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    closed_at DATETIME DEFAULT NULL,
    UNIQUE KEY uniq_tenant_ticketno (tenant_id, ticket_number),
    FOREIGN KEY (tenant_id) REFERENCES support_tenants(id) ON DELETE CASCADE,
    FOREIGN KEY (customer_id) REFERENCES support_customers(id),
    FOREIGN KEY (product_id) REFERENCES support_products(id),
    FOREIGN KEY (category_id) REFERENCES support_categories(id),
    FOREIGN KEY (assigned_agent_id) REFERENCES support_agents(id),
    INDEX idx_tenant_status (tenant_id, status),
    INDEX idx_tenant_priority (tenant_id, priority)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS support_ticket_comments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ticket_id INT NOT NULL,
    author_type ENUM('customer','agent') NOT NULL,
    author_id INT NOT NULL,
    body MEDIUMTEXT NOT NULL,
    is_internal_note TINYINT(1) NOT NULL DEFAULT 0,  -- agent-only note, not shown to customer
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (ticket_id) REFERENCES support_tickets(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS support_ticket_attachments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ticket_id INT NOT NULL,
    comment_id INT DEFAULT NULL,             -- NULL = attached at ticket creation
    original_name VARCHAR(255) NOT NULL,
    stored_path VARCHAR(500) NOT NULL,
    file_size INT NOT NULL,
    uploaded_by_type ENUM('customer','agent') NOT NULL,
    uploaded_by_id INT NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (ticket_id) REFERENCES support_tickets(id) ON DELETE CASCADE,
    FOREIGN KEY (comment_id) REFERENCES support_ticket_comments(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS support_ticket_status_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ticket_id INT NOT NULL,
    old_status VARCHAR(30) DEFAULT NULL,
    new_status VARCHAR(30) NOT NULL,
    changed_by_type ENUM('customer','agent','system') NOT NULL,
    changed_by_id INT DEFAULT NULL,
    changed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (ticket_id) REFERENCES support_tickets(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS support_ticket_ratings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ticket_id INT NOT NULL UNIQUE,
    rating TINYINT NOT NULL,      -- 1-5
    feedback TEXT DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (ticket_id) REFERENCES support_tickets(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS support_audit_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT DEFAULT NULL,
    actor_type ENUM('customer','agent','system') NOT NULL,
    actor_id INT DEFAULT NULL,
    action VARCHAR(100) NOT NULL,
    details TEXT DEFAULT NULL,
    ip_address VARCHAR(45) DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Seed: Midas as tenant #1, with the products you actually run today
INSERT INTO support_tenants (name, slug, contact_email, ticket_prefix) VALUES
('MidasSys', 'midas', 'info@midassys.com', 'TCK');

INSERT INTO support_products (tenant_id, name, sort_order) VALUES
(1, 'Midas Health Suite - Dental', 1),
(1, 'Midas Health Suite - Pharmacy', 2),
(1, 'MSuit CRM (WhatsApp)', 3),
(1, 'MidasInfraSuite', 4),
(1, 'Other / General', 5);

INSERT INTO support_categories (tenant_id, name, sort_order) VALUES
(1, 'Technical', 1),
(1, 'Billing', 2),
(1, 'Sales', 3),
(1, 'Marketing', 4),
(1, 'Feature Request', 5),
(1, 'Other', 6);

-- First agent (owner) — set password via the create_first_agent.php one-off script, don't insert plaintext here.
