-- 009_lead_status_deliveries.sql
-- Add status tracking to lead_requests and create delivery management tables

-- Lead status: new, contacted, scheduled, converted, lost
ALTER TABLE lead_requests
    ADD COLUMN status VARCHAR(20) DEFAULT 'new' AFTER converted,
    ADD COLUMN assigned_to INT NULL AFTER status,
    ADD COLUMN updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER assigned_to;

-- Deliveries: customer_id nullable for non-customer deliveries, address override fields
CREATE TABLE IF NOT EXISTS deliveries (
    delivery_id      INT AUTO_INCREMENT PRIMARY KEY,
    delivery_number  VARCHAR(30) NULL,
    customer_id      INT NULL,
    recipient_name   VARCHAR(120) NULL,
    delivery_address VARCHAR(200) NULL,
    delivery_city    VARCHAR(80) NULL,
    delivery_state   VARCHAR(10) NULL,
    delivery_zip     VARCHAR(10) NULL,
    technician_id    INT NULL,
    status           VARCHAR(20) DEFAULT 'pending',
    scheduled_date   DATE NULL,
    scheduled_window VARCHAR(40) NULL,
    signature_path   VARCHAR(255) NULL,
    gps_lat          DECIMAL(10,7) NULL,
    gps_lng          DECIMAL(10,7) NULL,
    gps_accuracy_m   FLOAT NULL,
    gps_captured_at  DATETIME NULL,
    notes            TEXT NULL,
    receipt_sent_at  DATETIME NULL,
    created_by       INT NULL,
    created_at       DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at       DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Patch existing installs: add new columns if missing
ALTER TABLE deliveries
    ADD COLUMN recipient_name VARCHAR(120) NULL AFTER status,
    ADD COLUMN delivery_address VARCHAR(200) NULL AFTER recipient_name,
    ADD COLUMN delivery_city VARCHAR(80) NULL AFTER delivery_address,
    ADD COLUMN delivery_state VARCHAR(10) NULL AFTER delivery_city,
    ADD COLUMN delivery_zip VARCHAR(10) NULL AFTER delivery_state;
ALTER TABLE deliveries
    MODIFY COLUMN customer_id INT NULL;
ALTER TABLE deliveries
    DROP FOREIGN KEY IF EXISTS fk_deliveries_customer;

CREATE TABLE IF NOT EXISTS delivery_lines (
    line_id        INT AUTO_INCREMENT PRIMARY KEY,
    delivery_id    INT NOT NULL,
    product_id     INT NOT NULL,
    qty_ordered    INT DEFAULT 0,
    qty_delivered  INT DEFAULT 0,
    empties_returned INT DEFAULT 0,
    notes          VARCHAR(255) NULL,
    FOREIGN KEY (delivery_id) REFERENCES deliveries(delivery_id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES delivery_products(product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS delivery_products (
    product_id     INT AUTO_INCREMENT PRIMARY KEY,
    name           VARCHAR(120) NOT NULL,
    container_size VARCHAR(40) NULL,
    unit_label     VARCHAR(40) NULL,
    is_active      TINYINT(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;