

-- PROJECT PHASES
CREATE TABLE IF NOT EXISTS ntc_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
CREATE TABLE IF NOT EXISTS ntc_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') DEFAULT 'not_started',
    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),
    FOREIGN KEY (phase_id) REFERENCES ntc_project_phases(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- MILESTONES
CREATE TABLE IF NOT EXISTS ntc_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 ntc_project_phases(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- PROJECT NOTES
CREATE TABLE IF NOT EXISTS ntc_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') 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 ntc_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 ntc_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 (6 tables)
-- =============================================

-- CONSUMERS (Prospective members database)
CREATE TABLE IF NOT EXISTS ntc_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('prospective_member', 'founder_circle', 'sports_enthusiast', 'luxury_lifestyle', 'corporate', 'general') DEFAULT 'general',
    estimated_income VARCHAR(50),
    age_range VARCHAR(20),
    gender VARCHAR(20),
    interest_tennis TINYINT(1) DEFAULT 0,
    interest_fitness TINYINT(1) DEFAULT 0,
    interest_social TINYINT(1) DEFAULT 0,
    interest_dining TINYINT(1) DEFAULT 0,
    lifestyle_data JSON,
    source VARCHAR(100),
    source_detail VARCHAR(255),
    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)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- LISTS (audience segments)
CREATE TABLE IF NOT EXISTS ntc_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 ntc_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 ntc_lists(id) ON DELETE CASCADE,
    FOREIGN KEY (consumer_id) REFERENCES ntc_consumers(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- CAMPAIGNS
CREATE TABLE IF NOT EXISTS ntc_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', 'digital_ad', 'event') DEFAULT 'email',
    status ENUM('draft', 'scheduled', 'sending', 'sent', 'paused', 'cancelled') DEFAULT 'draft',
    list_id INT,
    html_content LONGTEXT,
    text_content TEXT,
    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)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- EMAIL QUEUE
CREATE TABLE IF NOT EXISTS ntc_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 ntc_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;

-- Project Phases (6 phases, 24-month luxury club development)
INSERT INTO ntc_project_phases (phase_number, name, description, status, progress_percent, display_order, start_date, end_date)
VALUES
    (1, 'Brand & Membership Strategy', 'Identity, messaging, pricing, website design. Full brand package development including visual identity, membership tier strategy, and founding member positioning.', 'in_progress', 75, 1, '2025-10-01', '2025-11-30'),
    (2, 'Planning & Design', 'Courts, amenities, programs. Facility theming, interior design, amenity programming, and member experience design.', 'in_progress', 50, 2, '2025-12-01', '2026-01-31'),
    (3, 'Go-to-Market & Partnerships', 'Preview events, partnerships. Strategic partner acquisition, preview event planning, and founder circle recruitment.', 'in_progress', 25, 3, '2026-02-01', '2026-03-31'),
    (4, 'Digital Presence', 'Website/app, member portal. Full digital ecosystem build including website, mobile app specification, and member portal.', 'not_started', 0, 4, '2026-04-01', '2026-04-30'),
    (5, 'Content & Marketing', 'Brand content, digital campaigns. Content strategy, social media launch, digital advertising, and PR campaign.', 'not_started', 0, 5, '2026-05-01', '2026-07-31'),
    (6, 'Pre-Opening & Staffing', 'Pre-opening campaigns, recruitment, training. Grand opening preparation, staff recruitment, training programs, and launch campaigns.', 'not_started', 0, 6, '2026-08-01', '2027-06-30')
ON DUPLICATE KEY UPDATE name = VALUES(name);

-- Phase 1 Deliverables (Brand & Membership Strategy)
INSERT INTO ntc_deliverables (phase_id, title, description, category, status, client_visible, delivered_at)
VALUES
    (1, 'Brand Identity Package', 'Complete visual identity system including logo variants, color palette, typography, and brand guidelines for Novak Tennis Club / The Courts.', 'design', 'delivered', 1, '2025-11-01'),
    (1, 'Membership Pricing Matrix', 'Detailed membership tier pricing model with founding member, platinum, gold, and social membership levels including initiation fees and annual dues.', 'financial', 'delivered', 1, '2025-11-10'),
    (1, 'Brand Messaging Framework', 'Core messaging architecture including brand voice, taglines, value propositions, and positioning statements for The Golden Slam.', 'strategy', 'delivered', 1, '2025-11-12'),
    (1, 'Website Design Mockups', 'High-fidelity design mockups for thecourtsclub.com including homepage, membership, amenities, and events pages.', 'design', 'completed', 1, '2025-11-20'),
    (1, 'Sales Readiness Playbook', 'Internal sales team playbook with scripts, objection handling, founder circle pitch deck, and CRM workflow documentation.', 'strategy', 'in_progress', 0, NULL)
ON DUPLICATE KEY UPDATE title = VALUES(title);

-- Phase 2 Deliverables (Planning & Design)
INSERT INTO ntc_deliverables (phase_id, title, description, category, status, client_visible)
VALUES
    (2, 'Court Theming Concepts', 'Visual theming concepts for championship courts, practice courts, and exhibition areas with signature design elements.', 'design', 'completed', 1),
    (2, 'Interior Design Brief', 'Comprehensive interior design brief for clubhouse, lounge areas, pro shop, and member dining facilities.', 'design', 'in_progress', 0),
    (2, 'Amenity Programming Plan', 'Full programming plan for tennis instruction, fitness, wellness, social events, junior programs, and corporate hospitality.', 'strategy', 'in_progress', 0)
ON DUPLICATE KEY UPDATE title = VALUES(title);

-- Phase 3 Deliverables (Go-to-Market & Partnerships)
INSERT INTO ntc_deliverables (phase_id, title, description, category, status, client_visible)
VALUES
    (3, 'Partnership Strategy', 'Strategic partnership framework including luxury brand partners, equipment sponsors, hospitality partners, and reciprocal club agreements.', 'strategy', 'in_progress', 0),
    (3, 'Preview Event Concept', 'Detailed concept and production plan for exclusive founder circle preview events including venue, programming, and guest management.', 'strategy', 'not_started', 0),
    (3, 'Launch Marketing Plan', 'Comprehensive go-to-market plan including digital campaigns, PR strategy, influencer partnerships, and media outreach timeline.', 'strategy', 'not_started', 0)
ON DUPLICATE KEY UPDATE title = VALUES(title);

-- Phase 4 Deliverables (Digital Presence)
INSERT INTO ntc_deliverables (phase_id, title, description, category, status, client_visible)
VALUES
    (4, 'Website/App Specification', 'Complete technical specification for thecourtsclub.com website and companion mobile app including member portal, booking system, and event management.', 'design', 'not_started', 0)
ON DUPLICATE KEY UPDATE title = VALUES(title);

-- Milestones
INSERT INTO ntc_milestones (title, description, due_date, status, phase_id, client_visible)
VALUES
    ('Brand Package Approved', 'Client approval of complete brand identity package including logo, colors, typography, and guidelines.', '2025-11-15', 'completed', 1, 1),
    ('Sales Readiness', 'Sales team equipped with playbook, pitch materials, and CRM workflows for founder circle outreach.', '2025-12-01', 'in_progress', 1, 1),
    ('Go-to-Market Launch', 'Official launch of go-to-market campaign including partnerships, preview events, and founder recruitment.', '2026-03-01', 'upcoming', 3, 1),
    ('Website Launch', 'thecourtsclub.com goes live with full member portal and booking capabilities.', '2026-04-30', 'upcoming', 4, 1),
    ('Pre-Opening Campaign Start', 'Launch of comprehensive pre-opening marketing campaign across all channels.', '2026-10-01', 'upcoming', 6, 1),
    ('Grand Opening', 'Official grand opening of Novak Tennis Club / The Courts with founding member celebration event.', '2027-03-01', 'upcoming', 6, 1)
ON DUPLICATE KEY UPDATE title = VALUES(title);

-- Default Audience Lists
INSERT INTO ntc_lists (name, description, type, member_count)
VALUES
    ('Founder Circle Prospects', 'High-net-worth individuals targeted for founding membership at premium initiation tier.', 'static', 0),
    ('Sports Enthusiasts', 'Active tennis players, racquet sports enthusiasts, and competitive athletes in the target market.', 'static', 0),
    ('Corporate Members', 'Corporate executives and businesses interested in corporate membership and hospitality packages.', 'static', 0),
    ('Luxury Lifestyle', 'Luxury lifestyle consumers matching the club demographic profile.', 'static', 0),
    ('Full Database', 'Complete prospect database for Novak Tennis Club.', 'static', 0)
ON DUPLICATE KEY UPDATE name = VALUES(name);

-- Seed Notes
INSERT INTO ntc_project_notes (phase_id, author_id, author_name, content, note_type, client_visible)
VALUES
    (1, 1, 'Zack Bates', 'Brand identity package delivered and approved. Logo, color palette, typography, and brand guidelines finalized. The Courts / Golden Slam positioning resonating strongly with target audience.', 'client_update', 1),
    (1, 1, 'Zack Bates', 'Membership pricing matrix complete. Four tiers established: Founder, Platinum, Gold, Social. Initiation fees and annual dues benchmarked against comparable luxury tennis clubs nationally.', 'client_update', 1),
    (2, 1, 'Zack Bates', 'Court theming concepts presented. Championship court design approved. Moving to interior design brief for clubhouse and member amenities.', 'internal', 0)
ON DUPLICATE KEY UPDATE content = VALUES(content);
