

-- =====================================================
-- CONTACTS & CONSUMERS
-- =====================================================

CREATE TABLE IF NOT EXISTS brc_contacts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    name VARCHAR(255),
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    phone VARCHAR(50),
    mobile VARCHAR(50),
    company VARCHAR(255),
    job_title VARCHAR(255),
    member_type VARCHAR(100),
    membership_status VARCHAR(50),
    membership_start DATE,
    membership_end DATE,
    address VARCHAR(500),
    city VARCHAR(100),
    state VARCHAR(100),
    zip VARCHAR(20),
    country VARCHAR(100) DEFAULT 'USA',
    identity_cluster_id VARCHAR(50),
    lead_tier ENUM('hot', 'warm', 'cold'),
    engagement_score INT DEFAULT 0,
    unsubscribed TINYINT(1) DEFAULT 0,
    email_status ENUM('active', 'bounced', 'complained') DEFAULT 'active',
    source_url VARCHAR(500),
    enriched_at DATETIME,
    enrichment_source VARCHAR(50),
    apollo_id VARCHAR(100),
    linkedin_url VARCHAR(500),
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_email (email),
    INDEX idx_email (email),
    INDEX idx_cluster (identity_cluster_id),
    INDEX idx_member_type (member_type),
    INDEX idx_status (membership_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS brc_consumers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    name VARCHAR(255),
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    phone VARCHAR(50),
    city VARCHAR(100),
    state VARCHAR(100),
    zip VARCHAR(20),
    interests TEXT,
    identity_cluster_id VARCHAR(50),
    lead_tier ENUM('hot', 'warm', 'cold'),
    engagement_score INT DEFAULT 0,
    unsubscribed TINYINT(1) DEFAULT 0,
    email_status ENUM('active', 'bounced', 'complained') DEFAULT 'active',
    source VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_email (email),
    INDEX idx_email (email),
    INDEX idx_cluster (identity_cluster_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================
-- LISTS & SEGMENTATION
-- =====================================================

CREATE TABLE IF NOT EXISTS brc_lists (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    type ENUM('static', 'dynamic') DEFAULT 'static',
    contact_count INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS brc_list_members (
    id INT AUTO_INCREMENT PRIMARY KEY,
    list_id INT NOT NULL,
    contact_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_member (list_id, contact_id),
    INDEX idx_list (list_id),
    INDEX idx_contact (contact_id),
    FOREIGN KEY (list_id) REFERENCES brc_lists(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================
-- EMAIL CAMPAIGNS
-- =====================================================

CREATE TABLE IF NOT EXISTS brc_campaigns (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    subject VARCHAR(500),
    from_name VARCHAR(255) DEFAULT 'Bath & Racquet Club',
    from_email VARCHAR(255) DEFAULT 'hello@bathandracquetclub.org',
    reply_to VARCHAR(255) DEFAULT 'hello@bathandracquetclub.org',
    html_content LONGTEXT,
    text_content LONGTEXT,
    design_json LONGTEXT,
    status ENUM('draft', 'scheduled', 'queued', 'sending', 'paused', 'completed', 'cancelled') DEFAULT 'draft',
    list_id INT,
    list_ids TEXT,
    total_queued INT DEFAULT 0,
    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,
    unique_opens INT DEFAULT 0,
    unique_clicks INT DEFAULT 0,
    scheduled_at DATETIME,
    started_at DATETIME,
    completed_at DATETIME,
    timezone VARCHAR(50) DEFAULT 'America/New_York',
    resend_enabled TINYINT(1) DEFAULT 0,
    resend_days INT DEFAULT 3,
    resend_subject VARCHAR(255),
    resend_sent_at DATETIME,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_status (status),
    INDEX idx_scheduled (scheduled_at),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS brc_campaign_revisions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    campaign_id INT NOT NULL,
    name VARCHAR(255),
    subject VARCHAR(255),
    html_content LONGTEXT,
    design_json LONGTEXT,
    revision_note VARCHAR(255),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_campaign_id (campaign_id),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================
-- EMAIL QUEUE & TRACKING
-- =====================================================

CREATE TABLE IF NOT EXISTS brc_email_queue (
    id INT AUTO_INCREMENT PRIMARY KEY,
    campaign_id INT NOT NULL,
    contact_id INT,
    email VARCHAR(255) NOT NULL,
    name VARCHAR(255),
    email_hash VARCHAR(64),
    status ENUM('pending', 'processing', 'sent', 'failed') DEFAULT 'pending',
    is_verification TINYINT(1) DEFAULT 0,
    sent_at DATETIME,
    opened_at DATETIME,
    clicked_at DATETIME,
    resent_at DATETIME,
    error_message TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_campaign (campaign_id),
    INDEX idx_status (status),
    INDEX idx_email (email),
    INDEX idx_hash (email_hash),
    INDEX idx_sent (sent_at),
    FOREIGN KEY (campaign_id) REFERENCES brc_campaigns(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS brc_email_clicks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    campaign_id INT NOT NULL,
    email VARCHAR(255) NOT NULL,
    contact_id INT,
    url VARCHAR(2000),
    ip_address VARCHAR(45),
    user_agent TEXT,
    city VARCHAR(100),
    state VARCHAR(100),
    country VARCHAR(100),
    lat DECIMAL(10, 8),
    lng DECIMAL(11, 8),
    clicked_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_campaign (campaign_id),
    INDEX idx_email (email),
    INDEX idx_clicked (clicked_at),
    INDEX idx_ip (ip_address)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS brc_email_bounces (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    bounce_type ENUM('hard', 'soft', 'complaint') NOT NULL,
    campaign_id INT,
    reason TEXT,
    bounced_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_email (email),
    INDEX idx_type (bounce_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================
-- UNSUBSCRIBES
-- =====================================================

CREATE TABLE IF NOT EXISTS brc_domain_unsubscribes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    domain VARCHAR(255) NOT NULL DEFAULT 'bathandracquetclub.org',
    reason TEXT,
    unsubscribed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_unsub (email, domain),
    INDEX idx_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================
-- WEBSITE TRACKING
-- =====================================================

CREATE TABLE IF NOT EXISTS brc_website_visits (
    id INT AUTO_INCREMENT PRIMARY KEY,
    visitor_id VARCHAR(50),
    identity_cluster_id VARCHAR(50),
    email VARCHAR(255),
    page_url VARCHAR(500),
    page_path VARCHAR(255),
    page_title VARCHAR(500),
    referrer VARCHAR(500),
    utm_source VARCHAR(100),
    utm_medium VARCHAR(100),
    utm_campaign VARCHAR(100),
    ip_address VARCHAR(45),
    user_agent TEXT,
    city VARCHAR(100),
    state VARCHAR(100),
    country VARCHAR(100),
    lat DECIMAL(10, 8),
    lng DECIMAL(11, 8),
    visited_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_visitor (visitor_id),
    INDEX idx_cluster (identity_cluster_id),
    INDEX idx_email (email),
    INDEX idx_visited (visited_at),
    INDEX idx_ip (ip_address)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================
-- IDENTITY RESOLUTION
-- =====================================================

CREATE TABLE IF NOT EXISTS brc_identity_graph (
    id INT AUTO_INCREMENT PRIMARY KEY,
    identity_cluster_id VARCHAR(50) NOT NULL,
    identifier_type VARCHAR(50) NOT NULL,
    identifier_value VARCHAR(255) NOT NULL,
    confidence_score DECIMAL(3,2) DEFAULT 1.00,
    source VARCHAR(50) DEFAULT 'direct',
    is_primary TINYINT(1) DEFAULT 0,
    first_seen DATETIME DEFAULT CURRENT_TIMESTAMP,
    last_seen DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_identifier (identifier_type, identifier_value),
    INDEX idx_cluster (identity_cluster_id),
    INDEX idx_type_value (identifier_type, identifier_value)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS brc_device_graph (
    id INT AUTO_INCREMENT PRIMARY KEY,
    identity_cluster_id VARCHAR(50) NOT NULL,
    device_fingerprint VARCHAR(255) NOT NULL,
    device_type VARCHAR(50) DEFAULT 'desktop',
    os VARCHAR(100),
    browser VARCHAR(100),
    screen_resolution VARCHAR(50),
    session_count INT DEFAULT 1,
    first_seen DATETIME DEFAULT CURRENT_TIMESTAMP,
    last_seen DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_fingerprint (device_fingerprint),
    INDEX idx_cluster (identity_cluster_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS brc_ip_clusters (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ip_address VARCHAR(45) NOT NULL,
    identity_cluster_ids JSON,
    cluster_type ENUM('household', 'office', 'mobile') DEFAULT 'household',
    city VARCHAR(100),
    state VARCHAR(100),
    country VARCHAR(100),
    org VARCHAR(255),
    hit_count INT DEFAULT 1,
    first_seen DATETIME DEFAULT CURRENT_TIMESTAMP,
    last_seen DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_ip (ip_address)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS brc_visitor_sessions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    visitor_id VARCHAR(50) NOT NULL,
    identity_cluster_id VARCHAR(50),
    ip_address VARCHAR(45),
    email VARCHAR(255),
    city VARCHAR(100),
    region VARCHAR(100),
    country VARCHAR(100),
    company_name VARCHAR(255),
    device_fingerprint VARCHAR(255),
    total_pageviews INT DEFAULT 0,
    first_seen DATETIME DEFAULT CURRENT_TIMESTAMP,
    last_seen DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_visitor (visitor_id),
    INDEX idx_cluster (identity_cluster_id),
    INDEX idx_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS brc_identity_merge_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    surviving_cluster_id VARCHAR(50) NOT NULL,
    merged_cluster_id VARCHAR(50) NOT NULL,
    merge_reason VARCHAR(255),
    merged_by VARCHAR(100),
    merged_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_surviving (surviving_cluster_id),
    INDEX idx_merged (merged_cluster_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS brc_meta_leads (
    id INT AUTO_INCREMENT PRIMARY KEY,
    lead_id VARCHAR(50) NOT NULL,
    page_id VARCHAR(50) NOT NULL,
    form_id VARCHAR(50),
    email VARCHAR(255),
    phone VARCHAR(50),
    full_name VARCHAR(255),
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    field_data JSON,
    ad_id VARCHAR(50),
    adset_id VARCHAR(50),
    campaign_id VARCHAR(50),
    campaign_name VARCHAR(255),
    contact_id INT,
    identity_cluster_id VARCHAR(50),
    processed TINYINT(1) DEFAULT 0,
    created_time DATETIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_lead (lead_id),
    INDEX idx_page (page_id),
    INDEX idx_email (email),
    INDEX idx_processed (processed)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS brc_meta_events (
    id INT AUTO_INCREMENT PRIMARY KEY,
    site_id VARCHAR(100) DEFAULT 'bathandracquet',
    event_name VARCHAR(100) NOT NULL,
    event_time DATETIME NOT NULL,
    email VARCHAR(255),
    phone VARCHAR(50),
    identity_cluster_id VARCHAR(50),
    event_source_url VARCHAR(500),
    fbc VARCHAR(255),
    fbp VARCHAR(255),
    custom_data JSON,
    sent_to_meta TINYINT(1) DEFAULT 0,
    meta_response TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_site (site_id),
    INDEX idx_event (event_name),
    INDEX idx_time (event_time),
    INDEX idx_sent (sent_to_meta)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================
-- RELATIONSHIP MONITOR
-- =====================================================

CREATE TABLE IF NOT EXISTS brc_relationship_alerts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    contact_email VARCHAR(255) NOT NULL,
    contact_name VARCHAR(255),
    contact_id INT,
    alert_type ENUM('warming', 'cold', 'critical') NOT NULL DEFAULT 'cold',
    last_engagement_type VARCHAR(50),
    last_engagement_at DATETIME,
    days_since_engagement INT,
    status ENUM('pending', 'dismissed', 'actioned', 'resolved') DEFAULT 'pending',
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    actioned_at DATETIME,
    actioned_by VARCHAR(255),
    INDEX idx_email (contact_email),
    INDEX idx_type (alert_type),
    INDEX idx_status (status),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS brc_relationship_config (
    id INT PRIMARY KEY DEFAULT 1,
    config JSON NOT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Insert default relationship config
INSERT INTO brc_relationship_config (id, config) VALUES (1, '{
    "warm_threshold_days": 7,
    "cold_threshold_days": 14,
    "critical_threshold_days": 30,
    "check_email_opens": true,
    "check_email_clicks": true,
    "check_website_visits": true,
    "auto_alert": true,
    "slack_notifications": false
}') ON DUPLICATE KEY UPDATE id=id;

-- =====================================================
-- FAILSAFE / TEST VERIFICATION
-- =====================================================

CREATE TABLE IF NOT EXISTS brc_failsafe_verifications (
    id VARCHAR(50) PRIMARY KEY,
    campaign_id INT NOT NULL,
    email VARCHAR(255) NOT NULL,
    sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    opened_at TIMESTAMP NULL,
    clicked_at TIMESTAMP NULL,
    pixel_at TIMESTAMP NULL,
    INDEX idx_campaign (campaign_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================
-- AUDIT LOG
-- =====================================================

CREATE TABLE IF NOT EXISTS brc_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,
    old_value JSON,
    new_value JSON,
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user (user_id),
    INDEX idx_action (action),
    INDEX idx_entity (entity_type, entity_id),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================
-- DEFAULT DATA
-- =====================================================

-- Create default lists
INSERT INTO brc_lists (name, description, type) VALUES
    ('All Members', 'All active club members', 'static'),
    ('Prospects', 'Potential new members', 'static'),
    ('Newsletter', 'Newsletter subscribers', 'static'),
    ('Event Attendees', 'Past event attendees', 'static'),
    ('Lapsed Members', 'Former members who may rejoin', 'static')
ON DUPLICATE KEY UPDATE name=name;
