CREATE DATABASE IF NOT EXISTS solprimc_neura_leads CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE solprimc_neura_leads;

CREATE TABLE companies (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    legal_name VARCHAR(255) NULL,
    email VARCHAR(150) NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_id INT NULL,
    name VARCHAR(150) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('admin','supervisor','operator') NOT NULL DEFAULT 'operator',
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    last_login_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_users_company FOREIGN KEY (company_id) REFERENCES companies(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE clients (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    industry VARCHAR(150) NULL,
    country VARCHAR(100) NULL,
    city VARCHAR(100) NULL,
    timezone VARCHAR(80) NOT NULL DEFAULT 'America/Santiago',
    contact_name VARCHAR(150) NULL,
    contact_email VARCHAR(150) NULL,
    default_cc_email VARCHAR(150) NULL,
    notes TEXT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE integrations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    platform VARCHAR(80) NOT NULL,
    auth_type VARCHAR(50) NULL,
    credentials_json MEDIUMTEXT NULL,
    status VARCHAR(30) NOT NULL DEFAULT 'inactive',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE searches (
    id INT AUTO_INCREMENT PRIMARY KEY,
    client_id INT NOT NULL,
    user_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    objective VARCHAR(255) NULL,
    service_description TEXT NULL,
    buyer_persona TEXT NULL,
    primary_keyword VARCHAR(255) NOT NULL,
    related_phrases TEXT NULL,
    excluded_terms TEXT NULL,
    platforms_json TEXT NOT NULL,
    country VARCHAR(100) NULL,
    language VARCHAR(20) NULL,
    city VARCHAR(100) NULL,
    timezone VARCHAR(80) NOT NULL DEFAULT 'America/Santiago',
    execution_mode ENUM('manual','scheduled') NOT NULL DEFAULT 'scheduled',
    execution_frequency ENUM('hourly','daily','weekly','biweekly','monthly') NOT NULL DEFAULT 'daily',
    execution_interval INT NOT NULL DEFAULT 1,
    execution_time TIME NULL,
    execution_weekday TINYINT NULL,
    execution_monthday TINYINT NULL,
    first_run_at DATETIME NULL,
    last_run_at DATETIME NULL,
    next_run_at DATETIME NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_searches_client (client_id),
    INDEX idx_searches_next_run (next_run_at),
    CONSTRAINT fk_searches_client FOREIGN KEY (client_id) REFERENCES clients(id),
    CONSTRAINT fk_searches_user FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE delivery_configs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    search_id INT NOT NULL,
    delivery_mode ENUM('manual','scheduled','one_time') NOT NULL DEFAULT 'scheduled',
    frequency ENUM('daily','weekly','biweekly','monthly','one_time') NULL,
    interval_value INT NULL,
    delivery_time TIME NULL,
    delivery_weekday TINYINT NULL,
    delivery_monthday TINYINT NULL,
    timezone VARCHAR(80) NOT NULL DEFAULT 'America/Santiago',
    max_items_per_delivery INT NOT NULL DEFAULT 5,
    recipient_to VARCHAR(255) NOT NULL,
    recipient_cc VARCHAR(255) NULL,
    email_subject VARCHAR(255) NULL,
    email_template MEDIUMTEXT NULL,
    last_delivery_at DATETIME NULL,
    next_delivery_at DATETIME NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL,
    UNIQUE KEY uk_delivery_configs_search (search_id),
    INDEX idx_delivery_configs_next (next_delivery_at),
    CONSTRAINT fk_delivery_configs_search FOREIGN KEY (search_id) REFERENCES searches(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE leads (
    id INT AUTO_INCREMENT PRIMARY KEY,
    client_id INT NOT NULL,
    search_id INT NOT NULL,
    platform VARCHAR(80) NOT NULL,
    source_type VARCHAR(50) NULL,
    source_url VARCHAR(500) NULL,
    source_identifier VARCHAR(255) NULL,
    profile_name VARCHAR(255) NULL,
    profile_username VARCHAR(255) NULL,
    profile_url VARCHAR(500) NULL,
    content_text MEDIUMTEXT NULL,
    matched_term VARCHAR(255) NULL,
    contact_email VARCHAR(150) NULL,
    contact_phone VARCHAR(80) NULL,
    website VARCHAR(255) NULL,
    country VARCHAR(100) NULL,
    city VARCHAR(100) NULL,
    detected_at DATETIME NOT NULL,
    ai_summary TEXT NULL,
    ai_score DECIMAL(10,2) NULL,
    ai_reason TEXT NULL,
    status ENUM('new','reviewed','discarded') NOT NULL DEFAULT 'new',
    delivery_status ENUM('pending','excluded','discarded','sent','error') NOT NULL DEFAULT 'pending',
    excluded_from_delivery TINYINT(1) NOT NULL DEFAULT 0,
    discarded_at DATETIME NULL,
    sent_at DATETIME NULL,
    sent_report_id INT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_leads_search_pending (search_id, delivery_status, excluded_from_delivery),
    INDEX idx_leads_detected (detected_at),
    UNIQUE KEY uk_leads_dedupe (search_id, platform, source_identifier),
    CONSTRAINT fk_leads_client FOREIGN KEY (client_id) REFERENCES clients(id),
    CONSTRAINT fk_leads_search FOREIGN KEY (search_id) REFERENCES searches(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE search_runs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    search_id INT NOT NULL,
    run_type ENUM('manual','automatic') NOT NULL,
    started_at DATETIME NOT NULL,
    finished_at DATETIME NULL,
    timezone VARCHAR(80) NOT NULL,
    raw_results_count INT NOT NULL DEFAULT 0,
    new_leads_count INT NOT NULL DEFAULT 0,
    duplicate_count INT NOT NULL DEFAULT 0,
    error_count INT NOT NULL DEFAULT 0,
    status VARCHAR(30) NOT NULL,
    log_text MEDIUMTEXT NULL,
    created_by INT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_search_runs_search (search_id),
    CONSTRAINT fk_search_runs_search FOREIGN KEY (search_id) REFERENCES searches(id),
    CONSTRAINT fk_search_runs_user FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE deliveries (
    id INT AUTO_INCREMENT PRIMARY KEY,
    search_id INT NOT NULL,
    client_id INT NOT NULL,
    delivery_config_id INT NOT NULL,
    scheduled_at DATETIME NULL,
    executed_at DATETIME NULL,
    timezone VARCHAR(80) NOT NULL,
    items_attempted INT NOT NULL DEFAULT 0,
    items_sent INT NOT NULL DEFAULT 0,
    items_remaining INT NOT NULL DEFAULT 0,
    recipient_to VARCHAR(255) NOT NULL,
    recipient_cc VARCHAR(255) NULL,
    subject VARCHAR(255) NULL,
    status VARCHAR(30) NOT NULL,
    error_message TEXT NULL,
    created_by INT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_deliveries_search (search_id),
    CONSTRAINT fk_deliveries_search FOREIGN KEY (search_id) REFERENCES searches(id),
    CONSTRAINT fk_deliveries_client FOREIGN KEY (client_id) REFERENCES clients(id),
    CONSTRAINT fk_deliveries_config FOREIGN KEY (delivery_config_id) REFERENCES delivery_configs(id),
    CONSTRAINT fk_deliveries_user FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

UPDATE leads SET sent_report_id = NULL WHERE 1=0;
ALTER TABLE leads ADD CONSTRAINT fk_leads_delivery FOREIGN KEY (sent_report_id) REFERENCES deliveries(id);