-- =============================================
-- Harvard Club of Boston - Database Schema
-- =============================================
-- Run via: curl "https://harvardclub.privateclubmarketing.com/api/run-migration.php?key=harvardclub_migrate_2026"

-- =============================================
-- PROJECT MANAGEMENT TABLES
-- =============================================

-- USERS & AUTHENTICATION
CREATE TABLE IF NOT EXISTS hcb_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/New_York',
    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 hcb_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 hcb_deliverables (
    id INT AUTO_INCREMENT PRIMARY KEY,
    phase_id INT,
    title VARCHAR(500) NOT NULL,
    description TEXT,
    category ENUM('report', 'strategy', 'analysis', 'presentation', 'other') DEFAULT 'other',
    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 hcb_project_phases(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

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

-- CONSUMERS (alumni, board, committee, donors, prospects)
CREATE TABLE IF NOT EXISTS hcb_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('alumni', 'board_member', 'committee', 'donor', 'prospect', 'general') DEFAULT 'general',
    graduation_year VARCHAR(10),
    degree VARCHAR(100),
    membership_type VARCHAR(100),
    membership_status ENUM('active', 'lapsed', 'prospect', 'honorary') DEFAULT 'active',
    employer VARCHAR(255),
    industry VARCHAR(100),
    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),
    INDEX idx_membership (membership_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

-- CAMPAIGNS
CREATE TABLE IF NOT EXISTS hcb_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', 'event', 'newsletter') 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 hcb_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 hcb_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
-- =============================================
-- Note: User seed data is inserted by run-migration.php with proper password hashing

-- Project Phases (5-week consulting engagement)
INSERT INTO hcb_project_phases (phase_number, name, description, status, progress_percent, display_order, start_date, end_date)
VALUES
    (1, 'Discovery & Assessment', 'Kickoff meeting, stakeholder interviews, document review, current state assessment of communications and marketing operations.', 'completed', 100, 1, '2026-02-03', '2026-02-07'),
    (2, 'Analysis & Benchmarking', 'Current state analysis, communications audit, social media review, Harvard University alignment assessment, peer club benchmarking (5-7 clubs).', 'in_progress', 75, 2, '2026-02-10', '2026-02-14'),
    (3, 'Strategy Development', 'Recommended organizational structure, job descriptions package, implementation roadmap with timeline and budget.', 'in_progress', 25, 3, '2026-02-17', '2026-02-21'),
    (4, 'Report Development', 'Final comprehensive report compilation, executive presentation creation with key findings and recommendations.', 'not_started', 0, 4, '2026-02-24', '2026-02-28'),
    (5, 'Presentation & Handoff', 'In-person delivery of final report and presentation to leadership team, Q&A session, implementation guidance.', 'not_started', 0, 5, '2026-03-02', '2026-03-02')
ON DUPLICATE KEY UPDATE name = VALUES(name);

-- Phase 1 Deliverables (Discovery - all completed)
INSERT INTO hcb_deliverables (phase_id, title, description, category, status, assigned_to, client_visible, delivered_at)
VALUES
    (1, 'Kickoff Meeting Summary', 'Meeting notes from initial kickoff with leadership team covering project scope, goals, timeline, and key stakeholders.', 'report', 'delivered', 'Zack Bates', 1, '2026-02-03'),
    (1, 'Interview Notes & Document Inventory', 'Comprehensive notes from stakeholder interviews and inventory of all documents reviewed during discovery phase.', 'report', 'delivered', 'Zack Bates', 0, '2026-02-05'),
    (1, 'Stakeholder Interview Guide', 'Structured interview guide used for stakeholder conversations covering communications, marketing, events, and member engagement.', 'strategy', 'delivered', 'Zack Bates', 0, '2026-02-03'),
    (1, 'Current Org Chart Review', 'Analysis of current organizational structure for communications and marketing functions with role mapping.', 'analysis', 'delivered', 'Zack Bates', 1, '2026-02-07')
ON DUPLICATE KEY UPDATE title = VALUES(title);

-- Phase 2 Deliverables (Analysis - in progress)
INSERT INTO hcb_deliverables (phase_id, title, description, category, status, blocked_reason, assigned_to, client_visible)
VALUES
    (2, 'Current State Analysis', 'Comprehensive analysis of current communications, marketing, and member engagement operations including staffing, budget, and processes.', 'analysis', 'in_progress', NULL, 'Zack Bates', 0),
    (2, 'Communications & Social Media Audit', 'Full audit of all communications channels including email newsletters, social media accounts, website content, and member communications.', 'analysis', 'in_progress', NULL, 'Zack Bates', 0),
    (2, 'Harvard University Alignment Assessment', 'Assessment of current alignment with Harvard University brand guidelines, trademark usage, and partnership opportunities.', 'analysis', 'not_started', NULL, 'Zack Bates', 0),
    (2, 'Benchmarking Analysis (5-7 clubs)', 'Comparative analysis against 5-7 peer private clubs examining communications structure, staffing models, technology stack, and best practices.', 'analysis', 'in_progress', NULL, 'Zack Bates', 0)
ON DUPLICATE KEY UPDATE title = VALUES(title);

-- Phase 3 Deliverables (Strategy - not started)
INSERT INTO hcb_deliverables (phase_id, title, description, category, status, assigned_to, client_visible)
VALUES
    (3, 'Recommended Org Structure', 'Proposed organizational structure for communications and marketing department with reporting lines, role definitions, and growth plan.', 'strategy', 'not_started', 'Zack Bates', 0),
    (3, 'Job Descriptions Package', 'Complete job descriptions for all recommended positions including Director of Communications, Marketing Manager, Social Media Coordinator, and Content Specialist.', 'report', 'not_started', 'Zack Bates', 0),
    (3, 'Implementation Roadmap', 'Phased implementation plan with timeline, budget estimates, hiring priorities, technology recommendations, and quick wins.', 'strategy', 'not_started', 'Zack Bates', 0)
ON DUPLICATE KEY UPDATE title = VALUES(title);

-- Phase 4 Deliverables (Report)
INSERT INTO hcb_deliverables (phase_id, title, description, category, status, assigned_to, client_visible)
VALUES
    (4, 'Final Comprehensive Report', 'Complete written report covering all findings, analysis, benchmarking results, recommendations, and implementation roadmap.', 'report', 'not_started', 'Zack Bates', 0),
    (4, 'Executive Presentation', 'PowerPoint/Keynote presentation summarizing key findings and recommendations for board and leadership presentation.', 'presentation', 'not_started', 'Zack Bates', 0)
ON DUPLICATE KEY UPDATE title = VALUES(title);

-- Milestones
INSERT INTO hcb_milestones (title, description, due_date, status, phase_id, client_visible)
VALUES
    ('Discovery Week Complete', 'All stakeholder interviews conducted, documents reviewed, kickoff summary delivered.', '2026-02-07', 'completed', 1, 1),
    ('Benchmarking Analysis Due', 'Peer club benchmarking analysis (5-7 clubs) complete with comparative findings.', '2026-02-14', 'in_progress', 2, 1),
    ('Draft Org Chart Complete', 'Recommended organizational structure draft ready for leadership review.', '2026-02-21', 'upcoming', 3, 1),
    ('Draft Report Due', 'First draft of comprehensive report ready for internal review before finalization.', '2026-02-28', 'upcoming', 4, 1),
    ('Final Presentation to Leadership', 'In-person presentation of final report and recommendations to Harvard Club leadership team.', '2026-03-02', 'upcoming', 5, 1)
ON DUPLICATE KEY UPDATE title = VALUES(title);

-- Default Audience Lists
INSERT INTO hcb_lists (name, description, type, member_count)
VALUES
    ('Board Members', 'Harvard Club of Boston Board of Directors and advisory board members', 'static', 45),
    ('Committee Chairs', 'Chairs and vice-chairs of all active club committees', 'static', 32),
    ('Alumni Network', 'Active alumni members with current contact information', 'static', 4200),
    ('Donor Prospects', 'Members and prospects identified for fundraising outreach', 'static', 380),
    ('Full Database', 'Complete Harvard Club of Boston contact database', 'static', 5000)
ON DUPLICATE KEY UPDATE name = VALUES(name);

-- Seed Notes
INSERT INTO hcb_project_notes (phase_id, author_id, author_name, content, note_type, client_visible)
VALUES
    (1, 1, 'Zack Bates', 'Discovery phase complete. Conducted interviews with Sara LaWare (CFO), Steve Cummings (GM), and three department heads. Reviewed all current communications materials, social media accounts, and member surveys. Key finding: no dedicated communications/marketing leadership role currently exists.', 'client_update', 1),
    (1, 1, 'Zack Bates', 'Document inventory: reviewed membership directory, annual reports (2023-2025), event calendars, newsletter archives, social media analytics, website traffic data, and member satisfaction surveys. All documents cataloged in shared drive.', 'internal', 0),
    (2, 1, 'Zack Bates', 'Benchmarking clubs identified: University Club of Chicago, Harvard Club of New York, Union League Club of Philadelphia, Pacific-Union Club (SF), The Metropolitan Club (DC), The Algonquin Club (Boston). Outreach to contacts at each club initiated.', 'internal', 0)
ON DUPLICATE KEY UPDATE content = VALUES(content);
