-- ============================================================
-- quoERP — CRM / Pipeline de Ventas
-- Migracion: 030_crm.sql
-- ============================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- Oportunidades de venta (deals/leads)
CREATE TABLE IF NOT EXISTS crm_opportunities (
    id                  INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    company_id          INT UNSIGNED    NOT NULL,
    customer_id         INT UNSIGNED    DEFAULT NULL,
    customer_name_snap  VARCHAR(200)    DEFAULT NULL COMMENT 'Snapshot o nombre de prospecto sin cuenta',
    title               VARCHAR(200)    NOT NULL,
    value               DECIMAL(14,2)   NOT NULL DEFAULT 0 COMMENT 'Valor estimado de la oportunidad',
    stage               ENUM('lead','qualified','proposal','negotiation','won','lost')
                        NOT NULL DEFAULT 'lead',
    probability         TINYINT UNSIGNED NOT NULL DEFAULT 20 COMMENT '0-100%',
    expected_close_date DATE            DEFAULT NULL,
    source              ENUM('referral','web','cold_call','whatsapp','email','event','other')
                        DEFAULT 'other',
    assigned_to         INT UNSIGNED    DEFAULT NULL COMMENT 'Usuario responsable',
    quote_id            INT UNSIGNED    DEFAULT NULL COMMENT 'Cotizacion generada',
    lost_reason         VARCHAR(200)    DEFAULT NULL,
    description         TEXT            DEFAULT NULL,
    created_by          INT UNSIGNED    DEFAULT NULL,
    created_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          DATETIME        DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    INDEX idx_company   (company_id),
    INDEX idx_customer  (customer_id),
    INDEX idx_stage     (stage),
    INDEX idx_assigned  (assigned_to)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Actividades de seguimiento
CREATE TABLE IF NOT EXISTS crm_activities (
    id              INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    company_id      INT UNSIGNED    NOT NULL,
    opportunity_id  INT UNSIGNED    DEFAULT NULL,
    customer_id     INT UNSIGNED    DEFAULT NULL,
    type            ENUM('call','email','meeting','whatsapp','visit','task','other')
                    NOT NULL DEFAULT 'call',
    title           VARCHAR(200)    NOT NULL,
    notes           TEXT            DEFAULT NULL,
    scheduled_at    DATETIME        DEFAULT NULL,
    completed_at    DATETIME        DEFAULT NULL,
    is_completed    TINYINT(1)      NOT NULL DEFAULT 0,
    assigned_to     INT UNSIGNED    DEFAULT NULL,
    created_by      INT UNSIGNED    DEFAULT NULL,
    created_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    INDEX idx_company     (company_id),
    INDEX idx_opportunity (opportunity_id),
    INDEX idx_customer    (customer_id),
    INDEX idx_scheduled   (scheduled_at),
    INDEX idx_completed   (is_completed)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Contactos por cliente
CREATE TABLE IF NOT EXISTS crm_contacts (
    id          INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    company_id  INT UNSIGNED    NOT NULL,
    customer_id INT UNSIGNED    NOT NULL,
    name        VARCHAR(150)    NOT NULL,
    position    VARCHAR(100)    DEFAULT NULL,
    email       VARCHAR(150)    DEFAULT NULL,
    phone       VARCHAR(30)     DEFAULT NULL,
    whatsapp    VARCHAR(30)     DEFAULT NULL,
    is_primary  TINYINT(1)      NOT NULL DEFAULT 0,
    is_active   TINYINT(1)      NOT NULL DEFAULT 1,
    notes       TEXT            DEFAULT NULL,
    created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    INDEX idx_company  (company_id),
    INDEX idx_customer (customer_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;