-- ============================================================
-- KopierSFlow ERP — 006_deliveries_fleet_crm.sql
-- Importar SEXTO en phpMyAdmin
-- ============================================================

-- ── Rutas y Entregas ─────────────────────────────────────────

CREATE TABLE routes (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id      BIGINT UNSIGNED NOT NULL,
    route_name      VARCHAR(100)   NOT NULL,
    day_of_week     SET('mon','tue','wed','thu','fri','sat','sun'),
    warehouse_id    BIGINT UNSIGNED,
    default_driver  BIGINT UNSIGNED,
    default_vehicle BIGINT UNSIGNED,
    zone            VARCHAR(60),
    is_active       TINYINT(1) DEFAULT 1,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE route_stops (
    id                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    route_id          BIGINT UNSIGNED NOT NULL,
    customer_id       BIGINT UNSIGNED NOT NULL,
    stop_order        SMALLINT UNSIGNED DEFAULT 0,
    estimated_arrival TIME,
    address           TEXT,
    lat               DECIMAL(10,7),
    lng               DECIMAL(10,7),
    notes             TEXT,
    FOREIGN KEY (route_id)    REFERENCES routes(id)    ON DELETE CASCADE,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE delivery_tasks (
    id               BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id       BIGINT UNSIGNED NOT NULL,
    task_date        DATE           NOT NULL,
    route_id         BIGINT UNSIGNED,
    driver_id        BIGINT UNSIGNED,
    vehicle_id       BIGINT UNSIGNED,
    invoice_id       BIGINT UNSIGNED NOT NULL,
    customer_id      BIGINT UNSIGNED NOT NULL,
    stop_order       SMALLINT UNSIGNED DEFAULT 0,
    status           ENUM('pending','in_transit','delivered','partial','rejected','failed') DEFAULT 'pending',
    attempted_at     TIMESTAMP NULL,
    delivered_at     TIMESTAMP NULL,
    delivery_lat     DECIMAL(10,7),
    delivery_lng     DECIMAL(10,7),
    signature_path   VARCHAR(500),
    photo_paths      JSON,
    rejection_reason VARCHAR(255),
    partial_qty_note TEXT,
    amount_collected DECIMAL(14,4)  DEFAULT 0,
    payment_method   ENUM('cash','card','check','none') DEFAULT 'none',
    gateway_txn_id   VARCHAR(100),
    notes            TEXT,
    FOREIGN KEY (company_id)  REFERENCES companies(id)  ON DELETE CASCADE,
    FOREIGN KEY (customer_id) REFERENCES customers(id),
    INDEX idx_date_driver (company_id, task_date, driver_id),
    INDEX idx_status      (company_id, status, task_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE driver_app_sessions (
    id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    driver_id    BIGINT UNSIGNED NOT NULL,
    device_id    VARCHAR(200),
    fcm_token    VARCHAR(500),
    app_version  VARCHAR(20),
    platform     ENUM('android','ios'),
    last_lat     DECIMAL(10,7),
    last_lng     DECIMAL(10,7),
    last_seen_at TIMESTAMP NULL,
    is_active    TINYINT(1) DEFAULT 1,
    created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_driver (driver_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE push_notifications (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id  BIGINT UNSIGNED NOT NULL,
    user_id     BIGINT UNSIGNED NOT NULL,
    title       VARCHAR(200)   NOT NULL,
    body        TEXT,
    type        ENUM('route','invoice','temperature','expiration','recall','message','maintenance') NOT NULL,
    data        JSON,
    status      ENUM('queued','sent','failed') DEFAULT 'queued',
    sent_at     TIMESTAMP NULL,
    error       VARCHAR(500),
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user (user_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE offline_sync_queue (
    id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    driver_id  BIGINT UNSIGNED NOT NULL,
    action     VARCHAR(60)    NOT NULL,
    payload    JSON           NOT NULL,
    synced     TINYINT(1)     DEFAULT 0,
    synced_at  TIMESTAMP NULL,
    error      TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_driver_synced (driver_id, synced)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Flota y Mantenimiento ─────────────────────────────────────

CREATE TABLE vehicles (
    id               BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id       BIGINT UNSIGNED NOT NULL,
    vin              VARCHAR(20),
    plate            VARCHAR(20)    NOT NULL,
    year             SMALLINT UNSIGNED,
    make             VARCHAR(60),
    model            VARCHAR(100),
    type             ENUM('truck','van','refrigerated_truck','refrigerated_van','car','other') NOT NULL,
    capacity_lbs     DECIMAL(10,2),
    is_refrigerated  TINYINT(1)     DEFAULT 0,
    reefer_unit_make VARCHAR(60),
    temp_min_f       DECIMAL(6,2),
    temp_max_f       DECIMAL(6,2),
    fuel_type        ENUM('gasoline','diesel','electric','hybrid') DEFAULT 'diesel',
    mpg              DECIMAL(6,2),
    is_active        TINYINT(1)     DEFAULT 1,
    current_odometer DECIMAL(10,1)  DEFAULT 0,
    notes            TEXT,
    created_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE vehicle_documents (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    vehicle_id  BIGINT UNSIGNED NOT NULL,
    company_id  BIGINT UNSIGNED NOT NULL,
    doc_type    ENUM('insurance','registration','inspection','dot_permit','cdl','ifta','other') NOT NULL,
    doc_number  VARCHAR(100),
    issued_date DATE,
    expiry_date DATE,
    file_path   VARCHAR(500),
    notes       TEXT,
    FOREIGN KEY (vehicle_id) REFERENCES vehicles(id) ON DELETE CASCADE,
    INDEX idx_expiry (company_id, expiry_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE fuel_logs (
    id                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    vehicle_id        BIGINT UNSIGNED NOT NULL,
    company_id        BIGINT UNSIGNED NOT NULL,
    log_date          DATE           NOT NULL,
    odometer          DECIMAL(10,1)  NOT NULL,
    gallons           DECIMAL(8,3)   NOT NULL,
    price_per_gallon  DECIMAL(8,4),
    total_cost        DECIMAL(10,4),
    station           VARCHAR(100),
    driver_id         BIGINT UNSIGNED,
    FOREIGN KEY (vehicle_id) REFERENCES vehicles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE maintenance_schedules (
    id             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    vehicle_id     BIGINT UNSIGNED NOT NULL,
    company_id     BIGINT UNSIGNED NOT NULL,
    task_name      VARCHAR(150)   NOT NULL,
    trigger_type   ENUM('mileage','date','hours','calendar') NOT NULL,
    interval_value DECIMAL(10,2),
    next_due_date  DATE,
    next_due_miles DECIMAL(10,1),
    last_done_date DATE,
    last_done_miles DECIMAL(10,1),
    priority       ENUM('low','medium','high','critical') DEFAULT 'medium',
    FOREIGN KEY (vehicle_id) REFERENCES vehicles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE maintenance_work_orders (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id      BIGINT UNSIGNED NOT NULL,
    vehicle_id      BIGINT UNSIGNED NOT NULL,
    wo_number       VARCHAR(30)    NOT NULL,
    type            ENUM('preventive','corrective','inspection','tire','other') NOT NULL,
    description     TEXT,
    status          ENUM('open','in_progress','completed','cancelled') DEFAULT 'open',
    odometer        DECIMAL(10,1),
    vendor_id       BIGINT UNSIGNED,
    labor_cost      DECIMAL(10,4)  DEFAULT 0,
    parts_cost      DECIMAL(10,4)  DEFAULT 0,
    opened_date     DATE,
    completed_date  DATE,
    journal_entry_id BIGINT UNSIGNED,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    FOREIGN KEY (vehicle_id) REFERENCES vehicles(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── CRM y Fidelización ───────────────────────────────────────

CREATE TABLE loyalty_programs (
    id                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id        BIGINT UNSIGNED NOT NULL,
    name              VARCHAR(100)   NOT NULL,
    points_per_dollar DECIMAL(8,4)   DEFAULT 1.0,
    dollar_per_point  DECIMAL(8,4)   DEFAULT 0.01,
    is_active         TINYINT(1) DEFAULT 1,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE loyalty_accounts (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id      BIGINT UNSIGNED NOT NULL,
    customer_id     BIGINT UNSIGNED NOT NULL,
    program_id      BIGINT UNSIGNED NOT NULL,
    points_balance  DECIMAL(14,4)  DEFAULT 0,
    lifetime_points DECIMAL(14,4)  DEFAULT 0,
    lifetime_spend  DECIMAL(18,4)  DEFAULT 0,
    tier            ENUM('bronze','silver','gold','platinum') DEFAULT 'bronze',
    tier_updated_at TIMESTAMP NULL,
    FOREIGN KEY (company_id)  REFERENCES companies(id)  ON DELETE CASCADE,
    FOREIGN KEY (customer_id) REFERENCES customers(id)  ON DELETE CASCADE,
    UNIQUE KEY uq_customer_program (company_id, customer_id, program_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE loyalty_transactions (
    id                 BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    loyalty_account_id BIGINT UNSIGNED NOT NULL,
    type               ENUM('earn','redeem','adjust','expire') NOT NULL,
    points             DECIMAL(14,4)  NOT NULL,
    reference_type     VARCHAR(60),
    reference_id       BIGINT UNSIGNED,
    notes              VARCHAR(255),
    created_by         BIGINT UNSIGNED,
    created_at         TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (loyalty_account_id) REFERENCES loyalty_accounts(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE campaigns (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id  BIGINT UNSIGNED NOT NULL,
    name        VARCHAR(150)   NOT NULL,
    type        ENUM('email','sms','both') NOT NULL,
    status      ENUM('draft','scheduled','sending','sent','cancelled') DEFAULT 'draft',
    subject     VARCHAR(300),
    body        TEXT,
    scheduled_at TIMESTAMP NULL,
    sent_at     TIMESTAMP NULL,
    sent_count  INT DEFAULT 0,
    open_count  INT DEFAULT 0,
    created_by  BIGINT UNSIGNED,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE customer_interactions (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id  BIGINT UNSIGNED NOT NULL,
    customer_id BIGINT UNSIGNED NOT NULL,
    type        ENUM('call','email','visit','complaint','note','other') NOT NULL,
    subject     VARCHAR(300),
    body        TEXT,
    resolved    TINYINT(1) DEFAULT 0,
    resolved_at TIMESTAMP NULL,
    created_by  BIGINT UNSIGNED,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id)  REFERENCES companies(id) ON DELETE CASCADE,
    FOREIGN KEY (customer_id) REFERENCES customers(id),
    INDEX idx_customer (company_id, customer_id, type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;