-- Milink API - Base de Datos
-- Ejecutar en phpMyAdmin (seleccionar BD isocialpe_milink-api primero)
-- Si da error "Access denied", ejecuta esto primero en la pestaña SQL:
-- GRANT ALL PRIVILEGES ON isocialpe_milink-api.* TO 'isocialpe_milink-api'@'localhost';
-- FLUSH PRIVILEGES;

CREATE TABLE IF NOT EXISTS leads (
    id INT AUTO_INCREMENT PRIMARY KEY,
    lead_id VARCHAR(50) NOT NULL UNIQUE,
    name VARCHAR(255) NOT NULL,
    phone VARCHAR(20) DEFAULT '',
    b_rubric VARCHAR(100) DEFAULT '',
    b_label VARCHAR(255) DEFAULT '',
    business_name VARCHAR(255) DEFAULT '',
    business_description TEXT,
    main_service VARCHAR(255) DEFAULT '',
    location VARCHAR(100) DEFAULT '',
    province VARCHAR(100) DEFAULT '',
    schedule VARCHAR(50) DEFAULT '',
    website VARCHAR(255) DEFAULT '',
    interest VARCHAR(255) DEFAULT '',
    last_step INT DEFAULT 0,
    last_step_label VARCHAR(100) DEFAULT '',
    completed TINYINT DEFAULT 0,
    ip_address VARCHAR(45) DEFAULT '',
    user_agent TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_lead_id (lead_id),
    INDEX idx_completed (completed),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS lead_steps (
    id INT AUTO_INCREMENT PRIMARY KEY,
    lead_id VARCHAR(50) NOT NULL,
    step_number INT NOT NULL,
    step_label VARCHAR(100) DEFAULT '',
    reached_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_lead_step (lead_id, step_number),
    INDEX idx_step_number (step_number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS lead_crm (
    id INT AUTO_INCREMENT PRIMARY KEY,
    lead_id VARCHAR(50) NOT NULL UNIQUE,
    crm_status ENUM('nuevo','contactado','interesado','acepto','pensando','rechazo') DEFAULT 'nuevo',
    called_at DATETIME DEFAULT NULL,
    contacted_at DATETIME DEFAULT NULL,
    contact_note TEXT,
    project_start DATE DEFAULT NULL,
    project_end DATE DEFAULT NULL,
    notes TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_lead_crm (lead_id),
    INDEX idx_status (crm_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    lead_id VARCHAR(50) NOT NULL,
    installment_number INT NOT NULL COMMENT '1,2,3,4',
    label VARCHAR(50) DEFAULT '',
    amount DECIMAL(10,2) NOT NULL DEFAULT 625.00,
    due_days INT NOT NULL COMMENT '0,15,30,45',
    due_date DATE DEFAULT NULL,
    paid_date DATE DEFAULT NULL,
    paid TINYINT DEFAULT 0,
    payment_method VARCHAR(50) DEFAULT '',
    notes TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_lead_pay (lead_id, installment_number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;