-- BoricuaTour Business Finder
-- Base de datos INDEPENDIENTE de BoricuaTour Mapa.
-- MySQL 8+ / MariaDB 10.6+ compatible.

USE `logivxmx_boricuabusinessfinder`;

CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    email VARCHAR(190) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('admin','researcher','sales','viewer') NOT NULL DEFAULT 'researcher',
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    last_login_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_users_status(status)
) ENGINE=InnoDB;

CREATE TABLE countries (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    iso2 CHAR(2) NULL UNIQUE,
    iso3 CHAR(3) NULL UNIQUE,
    name VARCHAR(120) NOT NULL UNIQUE,
    active TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB;

CREATE TABLE regions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    country_id INT UNSIGNED NOT NULL,
    name VARCHAR(150) NOT NULL,
    code VARCHAR(30) NULL,
    UNIQUE KEY uq_region(country_id,name),
    FOREIGN KEY (country_id) REFERENCES countries(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE cities (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    region_id BIGINT UNSIGNED NULL,
    country_id INT UNSIGNED NOT NULL,
    name VARCHAR(150) NOT NULL,
    latitude DECIMAL(10,7) NULL,
    longitude DECIMAL(10,7) NULL,
    UNIQUE KEY uq_city(country_id,region_id,name),
    FOREIGN KEY (country_id) REFERENCES countries(id) ON DELETE CASCADE,
    FOREIGN KEY (region_id) REFERENCES regions(id) ON DELETE SET NULL,
    INDEX idx_city_name(name)
) ENGINE=InnoDB;

CREATE TABLE industries (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    parent_id INT UNSIGNED NULL,
    name VARCHAR(150) NOT NULL,
    slug VARCHAR(180) NOT NULL UNIQUE,
    description TEXT NULL,
    classification_system VARCHAR(30) NULL,
    classification_code VARCHAR(50) NULL,
    active TINYINT(1) NOT NULL DEFAULT 1,
    FOREIGN KEY (parent_id) REFERENCES industries(id) ON DELETE SET NULL,
    INDEX idx_industry_parent(parent_id)
) ENGINE=InnoDB;

CREATE TABLE companies (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    legal_name VARCHAR(255) NULL,
    display_name VARCHAR(255) NOT NULL,
    normalized_name VARCHAR(255) NOT NULL,
    website VARCHAR(500) NULL,
    domain VARCHAR(255) NULL,
    description TEXT NULL,
    industry_id INT UNSIGNED NULL,
    country_id INT UNSIGNED NULL,
    region_id BIGINT UNSIGNED NULL,
    city_id BIGINT UNSIGNED NULL,
    address_line VARCHAR(500) NULL,
    postal_code VARCHAR(30) NULL,
    latitude DECIMAL(10,7) NULL,
    longitude DECIMAL(10,7) NULL,
    employee_count_min INT UNSIGNED NULL,
    employee_count_max INT UNSIGNED NULL,
    founded_year SMALLINT UNSIGNED NULL,
    status ENUM('active','inactive','unknown') NOT NULL DEFAULT 'unknown',
    verification_status ENUM('unverified','partially_verified','verified') NOT NULL DEFAULT 'unverified',
    last_verified_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (industry_id) REFERENCES industries(id) ON DELETE SET NULL,
    FOREIGN KEY (country_id) REFERENCES countries(id) ON DELETE SET NULL,
    FOREIGN KEY (region_id) REFERENCES regions(id) ON DELETE SET NULL,
    FOREIGN KEY (city_id) REFERENCES cities(id) ON DELETE SET NULL,
    INDEX idx_company_name(normalized_name),
    INDEX idx_company_domain(domain),
    INDEX idx_company_location(country_id,region_id,city_id),
    INDEX idx_company_industry(industry_id),
    INDEX idx_company_status(status)
) ENGINE=InnoDB;

CREATE TABLE company_contacts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    contact_type ENUM('email','phone','website','social','other') NOT NULL,
    label VARCHAR(100) NULL,
    value VARCHAR(500) NOT NULL,
    is_public TINYINT(1) NOT NULL DEFAULT 1,
    is_verified TINYINT(1) NOT NULL DEFAULT 0,
    source_id BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    INDEX idx_contact_company(company_id),
    INDEX idx_contact_value(value(190))
) ENGINE=InnoDB;

CREATE TABLE sources (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    source_type ENUM('government','directory','company_website','association','search_engine','manual','api','other') NOT NULL,
    base_url VARCHAR(500) NULL,
    terms_url VARCHAR(500) NULL,
    active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE company_sources (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    source_id BIGINT UNSIGNED NOT NULL,
    source_record_id VARCHAR(255) NULL,
    source_url VARCHAR(1000) NULL,
    collected_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_checked_at DATETIME NULL,
    raw_hash CHAR(64) NULL,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    FOREIGN KEY (source_id) REFERENCES sources(id) ON DELETE CASCADE,
    UNIQUE KEY uq_company_source(company_id,source_id,source_record_id),
    INDEX idx_source_record(source_id,source_record_id)
) ENGINE=InnoDB;

CREATE TABLE prospects (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL UNIQUE,
    priority ENUM('low','normal','high','very_high') NOT NULL DEFAULT 'normal',
    score DECIMAL(6,2) NULL,
    status ENUM('new','researching','verified','invitation_ready','invited','engaged','registered','not_interested','invalid','do_not_contact') NOT NULL DEFAULT 'new',
    assigned_to BIGINT UNSIGNED NULL,
    notes TEXT NULL,
    discovered_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_action_at DATETIME NULL,
    next_action_at DATETIME NULL,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    FOREIGN KEY (assigned_to) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_prospect_status(status),
    INDEX idx_prospect_priority(priority),
    INDEX idx_prospect_score(score)
) ENGINE=InnoDB;

CREATE TABLE campaigns (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    description TEXT NULL,
    industry_id INT UNSIGNED NULL,
    country_id INT UNSIGNED NULL,
    region_id BIGINT UNSIGNED NULL,
    status ENUM('draft','active','paused','completed','archived') NOT NULL DEFAULT 'draft',
    created_by BIGINT UNSIGNED NULL,
    starts_at DATETIME NULL,
    ends_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (industry_id) REFERENCES industries(id) ON DELETE SET NULL,
    FOREIGN KEY (country_id) REFERENCES countries(id) ON DELETE SET NULL,
    FOREIGN KEY (region_id) REFERENCES regions(id) ON DELETE SET NULL,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE campaign_prospects (
    campaign_id BIGINT UNSIGNED NOT NULL,
    prospect_id BIGINT UNSIGNED NOT NULL,
    status ENUM('queued','approved','sent','engaged','converted','excluded') NOT NULL DEFAULT 'queued',
    added_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(campaign_id,prospect_id),
    FOREIGN KEY (campaign_id) REFERENCES campaigns(id) ON DELETE CASCADE,
    FOREIGN KEY (prospect_id) REFERENCES prospects(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE invitations (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    prospect_id BIGINT UNSIGNED NOT NULL,
    campaign_id BIGINT UNSIGNED NULL,
    channel ENUM('email','web','manual','other') NOT NULL DEFAULT 'email',
    recipient VARCHAR(500) NULL,
    subject VARCHAR(500) NULL,
    body LONGTEXT NULL,
    status ENUM('draft','approved','queued','sent','delivered','opened','clicked','responded','bounced','failed','cancelled') NOT NULL DEFAULT 'draft',
    provider_message_id VARCHAR(255) NULL,
    sent_at DATETIME NULL,
    created_by BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (prospect_id) REFERENCES prospects(id) ON DELETE CASCADE,
    FOREIGN KEY (campaign_id) REFERENCES campaigns(id) ON DELETE SET NULL,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_invitation_status(status),
    INDEX idx_invitation_prospect(prospect_id)
) ENGINE=InnoDB;

CREATE TABLE invitation_events (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    invitation_id BIGINT UNSIGNED NOT NULL,
    event_type ENUM('queued','sent','delivered','opened','clicked','responded','bounced','failed','unsubscribed') NOT NULL,
    event_data JSON NULL,
    occurred_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (invitation_id) REFERENCES invitations(id) ON DELETE CASCADE,
    INDEX idx_event_invitation(invitation_id),
    INDEX idx_event_type(event_type)
) ENGINE=InnoDB;

CREATE TABLE suppression_list (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NULL,
    contact_value VARCHAR(500) NULL,
    reason VARCHAR(255) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    INDEX idx_suppression_value(contact_value(190))
) ENGINE=InnoDB;

CREATE TABLE discovery_jobs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    job_type ENUM('search','import','crawl','verify','classify','deduplicate') NOT NULL,
    query_text TEXT NULL,
    parameters JSON NULL,
    status ENUM('queued','running','completed','failed','cancelled') NOT NULL DEFAULT 'queued',
    total_found INT UNSIGNED NOT NULL DEFAULT 0,
    processed_count INT UNSIGNED NOT NULL DEFAULT 0,
    error_message TEXT NULL,
    started_at DATETIME NULL,
    finished_at DATETIME NULL,
    created_by BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_job_status(status)
) ENGINE=InnoDB;

CREATE TABLE audit_log (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NULL,
    action VARCHAR(100) NOT NULL,
    entity_type VARCHAR(100) NULL,
    entity_id BIGINT UNSIGNED NULL,
    details JSON NULL,
    ip_address VARCHAR(45) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_audit_entity(entity_type,entity_id),
    INDEX idx_audit_created(created_at)
) ENGINE=InnoDB;

-- Datos iniciales de industrias.
INSERT INTO industries (name, slug, description, classification_system) VALUES
('Manufacturing','manufacturing','Manufactura y producción de bienes','NAICS'),
('Services','services','Servicios empresariales y profesionales','NAICS'),
('Technology','technology','Tecnología, software y servicios digitales','NAICS'),
('Healthcare','healthcare','Salud y servicios médicos','NAICS'),
('Pharmaceuticals','pharmaceuticals','Industria farmacéutica','NAICS'),
('Medical Devices','medical-devices','Dispositivos y equipos médicos','NAICS'),
('Construction','construction','Construcción e infraestructura','NAICS'),
('Transportation & Logistics','transportation-logistics','Transporte, carga y logística','NAICS'),
('Tourism & Hospitality','tourism-hospitality','Turismo, hoteles y hospitalidad','NAICS'),
('Food & Beverage','food-beverage','Alimentos y bebidas','NAICS'),
('Agriculture','agriculture','Agricultura y producción agropecuaria','NAICS'),
('Energy','energy','Energía y servicios relacionados','NAICS'),
('Financial Services','financial-services','Servicios financieros','NAICS'),
('Retail','retail','Comercio minorista','NAICS'),
('Wholesale & Distribution','wholesale-distribution','Comercio mayorista y distribución','NAICS'),
('Aerospace','aerospace','Aeroespacial y defensa comercial','NAICS'),
('Maritime','maritime','Industria marítima','NAICS'),
('Education','education','Educación y formación','NAICS'),
('Research & Development','research-development','Investigación y desarrollo','NAICS'),
('Environmental Services','environmental-services','Servicios ambientales y sostenibilidad','NAICS');

-- Puerto Rico como punto inicial.
INSERT INTO countries (iso2,iso3,name) VALUES ('PR','PRI','Puerto Rico')
ON DUPLICATE KEY UPDATE name=VALUES(name);