-- =============================================
-- Coastal Club - Database Schema
-- =============================================
-- Run via: curl "https://coastalclub.privateclubmarketing.com/api/run-migration.php?key=coastalclub_migrate_2026"

-- =============================================
-- Coastal Club - Database Schema
-- =============================================
-- Run via: curl "https://coastalclub.privateclubmarketing.com/api/run-migration.php?key=coastalclub_migrate_2026"

-- =============================================
-- PROJECT MANAGEMENT TABLES (hybrid pattern)
-- =============================================

-- USERS & AUTHENTICATION
CREATE TABLE IF NOT EXISTS cc_users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    name VARCHAR(255),
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    role ENUM('admin', 'manager', 'client') DEFAULT 'client',
    status ENUM('active', 'inactive', 'pending') DEFAULT 'active',
    profile_photo VARCHAR(500),
    phone VARCHAR(50),
    company VARCHAR(255),
    job_title VARCHAR(255),
    timezone VARCHAR(50) DEFAULT 'America/Los_Angeles',
    last_login DATETIME,
    login_count INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_email (email),
    INDEX idx_status (status),
    INDEX idx_role (role)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- PROJECT PHASES
CREATE TABLE IF NOT EXISTS cc_project_phases (
    id INT AUTO_INCREMENT PRIMARY KEY,
    phase_number INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    status ENUM('not_started', 'in_progress', 'completed', 'on_hold') DEFAULT 'not_started',
    start_date DATE,
    end_date DATE,
    actual_end_date DATE,
    progress_percent INT DEFAULT 0,
    client_visible TINYINT(1) DEFAULT 1,
    display_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_phase (phase_number),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- DELIVERABLES (extended with blocked status and assigned_to)
CREATE TABLE IF NOT EXISTS cc_deliverables (
    id INT AUTO_INCREMENT PRIMARY KEY,
    phase_id INT,
    title VARCHAR(500) NOT NULL,
    description TEXT,
    category ENUM('report', 'analysis', 'financial', 'strategy', 'design', 'presentation', 'other') DEFAULT 'report',
    status ENUM('not_started', 'in_progress', 'in_review', 'revision', 'completed', 'delivered', 'blocked') DEFAULT 'not_started',
    blocked_reason VARCHAR(500),
    assigned_to VARCHAR(255),
    file_url VARCHAR(1000),
    file_name VARCHAR(500),
    file_type VARCHAR(50),
    file_size INT,
    version INT DEFAULT 1,
    client_visible TINYINT(1) DEFAULT 0,
    client_notes TEXT,
    internal_notes TEXT,
    delivered_at DATETIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_phase (phase_id),
    INDEX idx_status (status),
    INDEX idx_category (category),
    INDEX idx_client_visible (client_visible),
    INDEX idx_assigned (assigned_to),
    FOREIGN KEY (phase_id) REFERENCES cc_project_phases(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- MILESTONES
CREATE TABLE IF NOT EXISTS cc_milestones (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(500) NOT NULL,
    description TEXT,
    due_date DATE,
    completed_date DATE,
    status ENUM('upcoming', 'in_progress', 'completed', 'overdue') DEFAULT 'upcoming',
    phase_id INT,
    client_visible TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_status (status),
    INDEX idx_due (due_date),
    FOREIGN KEY (phase_id) REFERENCES cc_project_phases(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- PROJECT NOTES (adds blocking note type)
CREATE TABLE IF NOT EXISTS cc_project_notes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    phase_id INT,
    author_id INT,
    author_name VARCHAR(255),
    content TEXT NOT NULL,
    note_type ENUM('internal', 'client_update', 'meeting_notes', 'decision', 'blocking') DEFAULT 'internal',
    client_visible TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_phase (phase_id),
    INDEX idx_type (note_type),
    INDEX idx_visible (client_visible)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- TIME ENTRIES
CREATE TABLE IF NOT EXISTS cc_time_entries (
    id INT AUTO_INCREMENT PRIMARY KEY,
    phase_id INT,
    deliverable_id INT,
    user_id INT,
    user_name VARCHAR(255),
    hours DECIMAL(5,2) NOT NULL,
    description TEXT,
    entry_date DATE NOT NULL,
    billable TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_phase (phase_id),
    INDEX idx_date (entry_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- AUDIT LOG
CREATE TABLE IF NOT EXISTS cc_audit_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    user_email VARCHAR(255),
    action VARCHAR(100) NOT NULL,
    entity_type VARCHAR(50),
    entity_id INT,
    details JSON,
    ip_address VARCHAR(45),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user (user_id),
    INDEX idx_action (action),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =============================================
-- CRM / MARKETING TABLES (hybrid pattern)
-- =============================================

-- CONSUMERS (consumer database)
CREATE TABLE IF NOT EXISTS cc_consumers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255),
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    name VARCHAR(255),
    phone VARCHAR(50),
    address VARCHAR(500),
    city VARCHAR(100),
    state VARCHAR(50),
    zip VARCHAR(20),
    country VARCHAR(50) DEFAULT 'US',
    segment ENUM('member', 'prospect', 'vip', 'lapsed', 'referral', 'general') DEFAULT 'general',
    distance_tier ENUM('0-3mi', '3-5mi', '5-10mi', '10-25mi', '25mi+') DEFAULT NULL,
    estimated_income VARCHAR(50),
    age_range VARCHAR(20),
    gender VARCHAR(20),
    interest_skincare TINYINT(1) DEFAULT 0,
    interest_wellness TINYINT(1) DEFAULT 0,
    interest_luxury TINYINT(1) DEFAULT 0,
    interest_fitness TINYINT(1) DEFAULT 0,
    lifestyle_data JSON,
    source VARCHAR(100),
    source_detail VARCHAR(255),
    boulevard_id VARCHAR(100),
    shopify_id VARCHAR(100),
    status ENUM('active', 'unsubscribed', 'bounced', 'inactive') DEFAULT 'active',
    last_visit DATE,
    visit_count INT DEFAULT 0,
    total_spend DECIMAL(10,2) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_email (email),
    INDEX idx_segment (segment),
    INDEX idx_city (city),
    INDEX idx_status (status),
    INDEX idx_distance (distance_tier)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- LISTS (audience segments)
CREATE TABLE IF NOT EXISTS cc_lists (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    type ENUM('static', 'dynamic', 'import') DEFAULT 'static',
    segment_criteria JSON,
    member_count INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- LIST MEMBERS
CREATE TABLE IF NOT EXISTS cc_list_members (
    id INT AUTO_INCREMENT PRIMARY KEY,
    list_id INT NOT NULL,
    consumer_id INT NOT NULL,
    added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_list (list_id),
    INDEX idx_consumer (consumer_id),
    UNIQUE KEY uk_list_consumer (list_id, consumer_id),
    FOREIGN KEY (list_id) REFERENCES cc_lists(id) ON DELETE CASCADE,
    FOREIGN KEY (consumer_id) REFERENCES cc_consumers(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- CAMPAIGNS (email + direct mail + geofenced ads)
CREATE TABLE IF NOT EXISTS cc_campaigns (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(500) NOT NULL,
    subject VARCHAR(500),
    from_name VARCHAR(255),
    from_email VARCHAR(255),
    type ENUM('email', 'direct_mail', 'geofenced_ad', 'sms') DEFAULT 'email',
    status ENUM('draft', 'scheduled', 'sending', 'sent', 'paused', 'cancelled') DEFAULT 'draft',
    list_id INT,
    html_content LONGTEXT,
    text_content TEXT,
    ab_test TINYINT(1) DEFAULT 0,
    ab_variant CHAR(1),
    ab_parent_id INT,
    ab_winner_criteria VARCHAR(50),
    scheduled_at DATETIME,
    sent_at DATETIME,
    completed_at DATETIME,
    total_recipients INT DEFAULT 0,
    total_sent INT DEFAULT 0,
    total_opened INT DEFAULT 0,
    total_clicked INT DEFAULT 0,
    total_bounced INT DEFAULT 0,
    total_unsubscribed INT DEFAULT 0,
    client_visible TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_status (status),
    INDEX idx_type (type),
    INDEX idx_list (list_id),
    INDEX idx_ab_parent (ab_parent_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- EMAIL QUEUE
CREATE TABLE IF NOT EXISTS cc_email_queue (
    id INT AUTO_INCREMENT PRIMARY KEY,
    campaign_id INT,
    consumer_id INT,
    email VARCHAR(255) NOT NULL,
    status ENUM('queued', 'sent', 'opened', 'clicked', 'bounced', 'failed') DEFAULT 'queued',
    sent_at DATETIME,
    opened_at DATETIME,
    clicked_at DATETIME,
    bounce_type VARCHAR(20),
    bounce_reason TEXT,
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_campaign (campaign_id),
    INDEX idx_consumer (consumer_id),
    INDEX idx_email (email),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- EMAIL CLICKS
CREATE TABLE IF NOT EXISTS cc_email_clicks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    queue_id INT,
    campaign_id INT,
    consumer_id INT,
    email VARCHAR(255),
    clicked_url VARCHAR(1000),
    ip_address VARCHAR(45),
    user_agent TEXT,
    city VARCHAR(100),
    state VARCHAR(50),
    country VARCHAR(50),
    latitude DECIMAL(10,7),
    longitude DECIMAL(10,7),
    clicked_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_campaign (campaign_id),
    INDEX idx_consumer (consumer_id),
    INDEX idx_url (clicked_url(255))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =============================================
-- SEED DATA

-- =============================================
-- SEED DATA
-- =============================================
-- Note: User seed data is inserted by run-migration.php with proper password hashing

-- Project Phases
INSERT INTO cc_project_phases (phase_number, name, description, status, progress_percent, display_order, start_date, end_date)
VALUES
    (1, 'Brand Foundation', 'Logo, brand guidelines, website design, and core marketing materials', 'in_progress', 60, 1, '2026-01-15', '2026-04-30'),
    (2, 'Member Acquisition', 'Digital advertising, email campaigns, referral program, and community outreach', 'in_progress', 15, 2, '2026-03-01', '2026-07-31'),
    (3, 'Events & Programming', 'Event calendar, member programming, partnerships, and seasonal activations', 'not_started', 0, 3, '2026-06-01', '2026-10-31')
ON DUPLICATE KEY UPDATE name = VALUES(name);

-- Deliverables
INSERT INTO cc_deliverables (phase_id, title, category, status, client_visible)
VALUES
    (1, 'Brand Guidelines Document', 'design', 'completed', 1),
    (1, 'Website Wireframes', 'design', 'completed', 1),
    (1, 'Website Development', 'design', 'in_progress', 1),
    (1, 'Marketing Collateral Suite', 'design', 'not_started', 1),
    (1, 'Competitive Analysis Report', 'report', 'completed', 0),
    (2, 'Email Campaign Strategy', 'strategy', 'in_progress', 1),
    (2, 'Social Media Content Plan', 'strategy', 'not_started', 1),
    (3, 'Q3 Event Calendar', 'strategy', 'not_started', 1)
ON DUPLICATE KEY UPDATE title = VALUES(title);

-- Milestones
INSERT INTO cc_milestones (title, due_date, status, phase_id, client_visible)
VALUES
    ('Brand Guidelines Approved', '2026-02-15', 'completed', 1, 1),
    ('Website Launch', '2026-04-15', 'upcoming', 1, 1),
    ('First Email Campaign Sent', '2026-04-01', 'upcoming', 2, 1),
    ('Summer Kickoff Event', '2026-06-15', 'upcoming', 3, 1)
ON DUPLICATE KEY UPDATE title = VALUES(title);

-- Default Audience Lists
INSERT INTO cc_lists (name, description, type, member_count)
VALUES
    ('All Members', 'All Members segment', 'static', 0),
    ('Prospective Members', 'Prospective Members segment', 'static', 0),
    ('VIP Members', 'VIP Members segment', 'static', 0),
    ('Event Attendees', 'Event Attendees segment', 'static', 0),
    ('Full Database', 'Full Database segment', 'static', 0)
ON DUPLICATE KEY UPDATE name = VALUES(name);
