

-- PROJECT PHASES
CREATE TABLE IF NOT EXISTS bdm_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 bdm_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 bdm_project_phases(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

-- PROJECT NOTES (adds blocking note type)
CREATE TABLE IF NOT EXISTS bdm_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 bdm_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 bdm_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 (BRC pattern)
-- =============================================

-- CONSUMERS (advertisers, subscribers, partners)
CREATE TABLE IF NOT EXISTS bdm_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('advertiser', 'subscriber', 'partner', 'luxury_brand', 'local_business', 'general') DEFAULT 'general',
    company VARCHAR(255),
    job_title VARCHAR(255),
    industry VARCHAR(100),
    website VARCHAR(500),
    estimated_revenue VARCHAR(50),
    ad_budget VARCHAR(50),
    source VARCHAR(100),
    source_detail VARCHAR(255),
    status ENUM('active', 'unsubscribed', 'bounced', 'inactive') DEFAULT 'active',
    last_contact DATE,
    contact_count INT DEFAULT 0,
    total_ad_spend DECIMAL(10,2) DEFAULT 0,
    notes TEXT,
    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_company (company)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

-- CAMPAIGNS (email + direct mail + geofenced ads + social)
CREATE TABLE IF NOT EXISTS bdm_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', 'social') 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 bdm_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 bdm_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
INSERT INTO bdm_project_phases (phase_number, name, description, status, progress_percent, display_order, start_date, end_date)
VALUES
    (1, 'Magazine Operations', 'Ad sales pipeline, media kit, distribution strategy, print production management', 'in_progress', 50, 1, '2025-01-01', NULL),
    (2, 'Digital Expansion', 'Website redesign, digital subscriber growth, newsletter strategy, online content', 'in_progress', 25, 2, '2025-06-01', '2026-06-30'),
    (3, 'Revenue Growth', 'Advertiser prospecting, rate card optimization, geo-fencing ads, CPM analysis', 'in_progress', 10, 3, '2025-10-01', '2026-09-30'),
    (4, 'Events & Activations', 'Launch events, brand partnerships, film division, community activations', 'not_started', 0, 4, '2026-03-01', '2026-12-31')
ON DUPLICATE KEY UPDATE name = VALUES(name);

-- Phase 1 Deliverables (Magazine Operations)
INSERT INTO bdm_deliverables (phase_id, title, description, category, status, client_visible, delivered_at)
VALUES
    (1, 'Media Kit 2026', 'Comprehensive media kit with audience demographics, ad rates, editorial calendar, and distribution reach', 'report', 'delivered', 1, '2025-04-15'),
    (1, 'Ad Sales Rate Card', 'Tiered pricing structure for print and digital ad placements with frequency discounts', 'financial', 'delivered', 1, '2025-03-01'),
    (1, 'Distribution Strategy', 'Targeted distribution plan for coastal Orange County with location analysis and pickup metrics', 'strategy', 'completed', 1, NULL),
    (1, 'Issue 31-35 Print Production', 'Ongoing print production management for issues 31 through 35', 'design', 'in_progress', 1, NULL),
    (1, 'Social Media Content Calendar', 'Monthly social media content plan aligned with editorial calendar and ad partner promotions', 'strategy', 'in_progress', 0, NULL)
ON DUPLICATE KEY UPDATE title = VALUES(title);

-- Phase 2 Deliverables (Digital Expansion)
INSERT INTO bdm_deliverables (phase_id, title, description, category, status, client_visible)
VALUES
    (2, 'Website Redesign Proposal', 'Full website redesign proposal with wireframes, content strategy, and digital subscriber funnel', 'design', 'completed', 1),
    (2, 'Email Newsletter Template', 'HTML email newsletter template matching Blue Door brand for weekly/monthly sends', 'design', 'in_progress', 0),
    (2, 'Digital Subscriber Growth Plan', 'Strategy for growing digital subscriber base to 50K with acquisition channels and conversion optimization', 'strategy', 'not_started', 0)
ON DUPLICATE KEY UPDATE title = VALUES(title);

-- Phase 3 Deliverables (Revenue Growth)
INSERT INTO bdm_deliverables (phase_id, title, description, category, status, client_visible)
VALUES
    (3, 'Geo-Fencing Ad Presentation', 'Presentation for advertisers on geo-fenced digital advertising targeting luxury OC demographics', 'presentation', 'completed', 1),
    (3, 'CPM Cost Analysis', 'Detailed cost-per-thousand analysis comparing Blue Door ad rates to competitive OC publications', 'financial', 'in_progress', 0)
ON DUPLICATE KEY UPDATE title = VALUES(title);

-- Milestones
INSERT INTO bdm_milestones (title, description, due_date, status, phase_id, client_visible)
VALUES
    ('Media Kit 2026 Launch', 'Complete and distribute updated media kit to current and prospective advertisers', '2025-03-15', 'completed', 1, 1),
    ('Website Redesign Complete', 'New bluedoormagazine.com website live with digital subscriber integration', '2026-04-15', 'in_progress', 2, 1),
    ('Digital Subscriber 50K Goal', 'Reach 50,000 digital subscribers across email and social platforms', '2026-09-01', 'upcoming', 2, 1),
    ('Q3 Revenue Target', 'Achieve quarterly revenue target from combined print and digital ad sales', '2026-09-30', 'upcoming', 3, 1),
    ('First Brand Activation Event', 'Host inaugural Blue Door Magazine brand activation event in coastal OC', '2026-06-15', 'upcoming', 4, 1)
ON DUPLICATE KEY UPDATE title = VALUES(title);

-- Default Audience Lists
INSERT INTO bdm_lists (name, description, type, member_count)
VALUES
    ('All Advertisers', 'All current and past advertisers across print and digital', 'static', 0),
    ('Premium Partners', 'Premium advertising partners with annual contracts', 'static', 0),
    ('Digital Subscribers', 'Email and digital newsletter subscribers', 'static', 0),
    ('Luxury Brands', 'Luxury brand contacts for premium ad placements and partnerships', 'static', 0),
    ('Local Businesses', 'Local Orange County business contacts for community ad section', 'static', 0),
    ('Full Database', 'Complete advertiser, subscriber, and partner database', 'static', 0)
ON DUPLICATE KEY UPDATE name = VALUES(name);

-- Seed Notes
INSERT INTO bdm_project_notes (phase_id, author_id, author_name, content, note_type, client_visible)
VALUES
    (1, 1, 'Zack Bates', 'Media kit and rate card delivered. Distribution strategy finalized for coastal OC locations. Issue 31-35 production pipeline active.', 'client_update', 1),
    (2, 1, 'Zack Bates', 'Website redesign proposal approved by Justin. Moving into design phase. Newsletter template in development.', 'internal', 0),
    (3, 1, 'Zack Bates', 'Geo-fencing presentation completed and well received. CPM analysis underway to support competitive positioning.', 'internal', 0)
ON DUPLICATE KEY UPDATE content = VALUES(content);
