-- ============================================================
-- Nettrans Schema Updates
-- Run this in phpMyAdmin AFTER the original database.sql
-- ============================================================

USE nettrans;

-- ── CUSTOMER ADDRESSES ──────────────────────────────────────
CREATE TABLE IF NOT EXISTS customer_addresses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    address_label VARCHAR(100),
    company_name VARCHAR(200),
    address VARCHAR(255),
    city VARCHAR(100),
    state VARCHAR(50),
    zip VARCHAR(20),
    country VARCHAR(80) DEFAULT 'USA',
    contact_name VARCHAR(150),
    contact_phone VARCHAR(30),
    is_default TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
);

-- ── SHOWS TABLE ──────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS shows (
    id INT AUTO_INCREMENT PRIMARY KEY,
    show_name VARCHAR(200) NOT NULL,
    convention_center VARCHAR(200),
    city VARCHAR(100),
    state VARCHAR(50),
    hall VARCHAR(100),
    movein_date DATE,
    moveout_date DATE,
    show_dates VARCHAR(200),
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ── ADD MISSING COLUMNS TO ORDERS ───────────────────────────
ALTER TABLE orders
    ADD COLUMN IF NOT EXISTS show_id INT DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS moveout_date DATE DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS origin_address_type ENUM('manual','location','customer_address') DEFAULT 'manual',
    ADD COLUMN IF NOT EXISTS dest_address_type ENUM('manual','location','customer_address') DEFAULT 'manual';