ALTER TABLE users
    ADD COLUMN IF NOT EXISTS lock_until DATETIME NULL AFTER password_changed_at;

CREATE TABLE IF NOT EXISTS contacts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    first_name VARCHAR(120) NOT NULL,
    last_name VARCHAR(120) NULL,
    email VARCHAR(190) NULL,
    phone VARCHAR(50) NULL,
    company VARCHAR(160) NULL,
    status ENUM('lead', 'prospect', 'customer', 'inactive') NOT NULL DEFAULT 'lead',
    owner_user_id BIGINT UNSIGNED NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_contacts_tenant_status (tenant_id, status),
    INDEX idx_contacts_tenant_name (tenant_id, first_name, last_name),
    CONSTRAINT fk_contacts_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_contacts_owner FOREIGN KEY (owner_user_id) REFERENCES users(id) ON DELETE SET NULL,
    UNIQUE KEY uq_contacts_tenant_email (tenant_id, email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pipeline_stages (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(100) NOT NULL,
    sort_order INT UNSIGNED NOT NULL DEFAULT 0,
    win_probability TINYINT UNSIGNED NOT NULL DEFAULT 0,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_pipeline_stages_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    UNIQUE KEY uq_pipeline_stage_name (tenant_id, name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS opportunities (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    contact_id BIGINT UNSIGNED NULL,
    stage_id BIGINT UNSIGNED NOT NULL,
    title VARCHAR(190) NOT NULL,
    amount DECIMAL(12,2) NOT NULL DEFAULT 0,
    expected_close_date DATE NULL,
    status ENUM('open', 'won', 'lost') NOT NULL DEFAULT 'open',
    created_by BIGINT UNSIGNED NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_opportunities_tenant_stage (tenant_id, stage_id),
    CONSTRAINT fk_opportunities_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_opportunities_contact FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE SET NULL,
    CONSTRAINT fk_opportunities_stage FOREIGN KEY (stage_id) REFERENCES pipeline_stages(id) ON DELETE CASCADE,
    CONSTRAINT fk_opportunities_creator FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS automation_rules (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(140) NOT NULL,
    trigger_event VARCHAR(100) NOT NULL,
    action_type VARCHAR(100) NOT NULL,
    config_json JSON NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    last_run_at DATETIME NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_automation_rules_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS notifications (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NULL,
    title VARCHAR(190) NOT NULL,
    body TEXT NULL,
    read_at DATETIME NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_notifications_tenant_read (tenant_id, read_at),
    CONSTRAINT fk_notifications_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_notifications_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS error_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NULL,
    level ENUM('error', 'warning', 'info') NOT NULL DEFAULT 'error',
    context VARCHAR(140) NOT NULL,
    message TEXT NOT NULL,
    trace_snippet TEXT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_error_logs_tenant_created (tenant_id, created_at),
    CONSTRAINT fk_error_logs_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO pipeline_stages (tenant_id, name, sort_order, win_probability)
SELECT t.id, 'Lead', 1, 10 FROM tenants t WHERE t.slug = 'demo'
UNION ALL
SELECT t.id, 'Contacto', 2, 30 FROM tenants t WHERE t.slug = 'demo'
UNION ALL
SELECT t.id, 'Propuesta', 3, 60 FROM tenants t WHERE t.slug = 'demo'
UNION ALL
SELECT t.id, 'Cierre', 4, 90 FROM tenants t WHERE t.slug = 'demo'
ON DUPLICATE KEY UPDATE sort_order = VALUES(sort_order), win_probability = VALUES(win_probability);

INSERT INTO contacts (tenant_id, first_name, last_name, email, phone, company, status)
SELECT t.id, 'Carlos', 'Ramirez', 'carlos@empresa.com', '555-0101', 'Empresa Uno', 'prospect' FROM tenants t WHERE t.slug = 'demo'
UNION ALL
SELECT t.id, 'Ana', 'Lopez', 'ana@negocio.com', '555-0102', 'Negocio Dos', 'lead' FROM tenants t WHERE t.slug = 'demo'
ON DUPLICATE KEY UPDATE first_name = VALUES(first_name);

INSERT INTO opportunities (tenant_id, contact_id, stage_id, title, amount, expected_close_date, status)
SELECT t.id, c.id, ps.id, 'Implementacion CRM Empresa Uno', 12000, DATE_ADD(CURDATE(), INTERVAL 20 DAY), 'open'
FROM tenants t
JOIN contacts c ON c.tenant_id = t.id AND c.email = 'carlos@empresa.com'
JOIN pipeline_stages ps ON ps.tenant_id = t.id AND ps.name = 'Propuesta'
WHERE t.slug = 'demo'
LIMIT 1;

INSERT INTO automation_rules (tenant_id, name, trigger_event, action_type, config_json, is_active)
SELECT t.id, 'Nuevo lead -> notificar owner', 'contact.created', 'notify_owner', JSON_OBJECT('channel', 'in_app'), 1
FROM tenants t
WHERE t.slug = 'demo'
ON DUPLICATE KEY UPDATE is_active = VALUES(is_active);
