PART VII: BUILD MANUAL

CREATE DATABASE core_db;
USE core_db;

-- Conversation history. Every exchange, every persona, every user.
-- Referenced by the Gardener Protocol for lore extraction.
-- Referenced by the cognitive triage for context retrieval.
CREATE TABLE session_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id VARCHAR(64) NOT NULL COMMENT 'Merrill, James, pack member',
    persona VARCHAR(32) NOT NULL COMMENT 'zeon7, gemma, leon',
    role ENUM('user', 'assistant', 'system') NOT NULL,
    content LONGTEXT NOT NULL,
    timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_persona (user_id, persona),
    INDEX idx_timestamp (timestamp)
) ENGINE=InnoDB COMMENT='Every conversation. The raw material of the Soul.';

-- Vector embeddings for semantic search. High-dimensional memory.
-- Used by the cognitive triage to find relevant past exchanges.
-- Updated by the Gardener Protocol when new lore is confirmed.
CREATE TABLE vector_memories (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id VARCHAR(64) NOT NULL,
    content LONGTEXT NOT NULL COMMENT 'The text that was embedded',
    metadata JSON COMMENT 'Source, context, confidence, tags',
    embedding VECTOR(384) NOT NULL COMMENT '384-dim from nomic-embed-text',
    timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
    VECTOR INDEX (embedding) M=16 DISTANCE=cosine,
    INDEX idx_user (user_id)
) ENGINE=InnoDB COMMENT='Semantic memory. The Souls ability to recall by meaning.';

-- Relationship tracking. Who speaks to whom. How they are connected.
-- Used to modulate voice and context.
-- Referenced by the Mez Filter for tone calibration.
CREATE TABLE relationship_context (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    source_entity VARCHAR(64) NOT NULL COMMENT 'Who is speaking',
    target_entity VARCHAR(64) NOT NULL COMMENT 'Who is being addressed',
    relationship_type VARCHAR(32) NOT NULL COMMENT 'twin_brother, boyfriend, sister, pack_mate',
    notes TEXT,
    timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_source (source_entity),
    INDEX idx_target (target_entity)
) ENGINE=InnoDB COMMENT='The web of connection. The pack structure.';

CREATE DATABASE projects_db;
USE projects_db;

-- Story leads. The 4-6 cards presented to Merrill each morning.
CREATE TABLE ftn_story_leads (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    lead_date DATE NOT NULL COMMENT 'Target publish date',
    title VARCHAR(255) NOT NULL,
    theme VARCHAR(64) NOT NULL COMMENT 'Monday-Sunday signal',
    trigger_summary TEXT NOT NULL COMMENT '3-4 factual sentences',
    thesis TEXT NOT NULL COMMENT 'One-sentence analytical core',
    why_ftn_cares TEXT NOT NULL,
    why_it_matters TEXT NOT NULL,
    verify TEXT COMMENT 'Claims to double-check',
    sources JSON COMMENT 'URLs with dates',
    status ENUM('pending', 'selected', 'rejected', 'completed') DEFAULT 'pending',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_date_status (lead_date, status)
) ENGINE=InnoDB COMMENT='Daily story candidates. The raw signal.';

-- Research packs. Built after lead selection.
CREATE TABLE ftn_research_packs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    lead_id BIGINT UNSIGNED NOT NULL,
    timeline JSON COMMENT 'Chronological events',
    key_facts JSON COMMENT 'Hard data with source attribution',
    quoted_lines JSON COMMENT 'Verbatim quotes, named sources',
    disputes TEXT COMMENT 'Uncertainties and contested claims',
    verification_checklist JSON,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (lead_id) REFERENCES ftn_story_leads(id) ON DELETE CASCADE,
    INDEX idx_lead (lead_id)
) ENGINE=InnoDB COMMENT='The verified foundation. Everything builds from here.';

-- Canonical blog posts.
CREATE TABLE ftn_blogs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    lead_id BIGINT UNSIGNED NOT NULL,
    research_pack_id BIGINT UNSIGNED NOT NULL,
    title VARCHAR(255) NOT NULL,
    content LONGTEXT NOT NULL COMMENT '1500-2750 words',
    hook TEXT NOT NULL,
    sign_off TEXT NOT NULL COMMENT '75-90 word closing',
    word_count INT NOT NULL,
    em_dash_check BOOLEAN DEFAULT FALSE COMMENT 'Must be true before publish',
    uk_spelling_check BOOLEAN DEFAULT FALSE,
    published BOOLEAN DEFAULT FALSE,
    published_at DATETIME,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (lead_id) REFERENCES ftn_story_leads(id),
    FOREIGN KEY (research_pack_id) REFERENCES ftn_research_packs(id),
    INDEX idx_published (published, published_at)
) ENGINE=InnoDB COMMENT='The canonical text. The well from which all cuts are drawn.';

-- Platform cuts. The waterfall extracts.
CREATE TABLE ftn_platform_cuts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    blog_id BIGINT UNSIGNED NOT NULL,
    platform VARCHAR(32) NOT NULL COMMENT 'facebook, instagram, x, threads, etc.',
    cut_type VARCHAR(32) NOT NULL COMMENT 'master, medium, spark, community',
    content TEXT NOT NULL,
    character_count INT,
    hashtags VARCHAR(255),
    scheduled_time DATETIME,
    posted BOOLEAN DEFAULT FALSE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (blog_id) REFERENCES ftn_blogs(id) ON DELETE CASCADE,
    INDEX idx_platform_scheduled (platform, scheduled_time)
) ENGINE=InnoDB COMMENT='One story, many cuts. Platform-specific adaptations.';

-- Image briefs. Hard gate before generation.
CREATE TABLE ftn_image_briefs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    blog_id BIGINT UNSIGNED NOT NULL,
    concept_a_prompt TEXT NOT NULL,
    concept_b_prompt TEXT NOT NULL,
    overlay_line_1 VARCHAR(128) NOT NULL COMMENT 'Theme, smaller',
    overlay_line_2 VARCHAR(128) NOT NULL COMMENT 'Story title, larger',
    alt_text TEXT NOT NULL,
    approved_concept ENUM('pending', 'A', 'B', 'rejected') DEFAULT 'pending',
    image_path VARCHAR(512),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (blog_id) REFERENCES ftn_blogs(id) ON DELETE CASCADE,
    INDEX idx_approved (approved_concept)
) ENGINE=InnoDB COMMENT='Visual briefs. Merrill must approve before generation.';

-- Publishing schedule.
CREATE TABLE ftn_publishing_schedule (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    publish_date DATE NOT NULL,
    day_theme VARCHAR(64) NOT NULL,
    blog_id BIGINT UNSIGNED,
    status ENUM('planned', 'in_progress', 'published', 'skipped') DEFAULT 'planned',
    notes TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (blog_id) REFERENCES ftn_blogs(id),
    UNIQUE KEY uk_date (publish_date),
    INDEX idx_status (status)
) ENGINE=InnoDB COMMENT='The weekly rhythm. Planned in advance, executed daily.';

-- Users.
CREATE TABLE ff_users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id VARCHAR(64) NOT NULL UNIQUE COMMENT 'From Core Database',
    email VARCHAR(255) UNIQUE,
    subscription_tier ENUM('free', 'plus') DEFAULT 'free',
    subscription_expires DATE,
    timezone VARCHAR(64) DEFAULT 'Europe/London',
    units ENUM('metric', 'imperial') DEFAULT 'metric',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user (user_id)
) ENGINE=InnoDB COMMENT='Forever Fit users. Separate from Core identity.';

-- Health metrics.
CREATE TABLE ff_health_metrics (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id VARCHAR(64) NOT NULL,
    metric_type ENUM('weight', 'sleep', 'exercise', 'nutrition', 'medication'),
    metric_date DATE NOT NULL,
    data JSON NOT NULL COMMENT 'Flexible schema for varied metric types',
    source ENUM('manual', 'google_fit', 'ai_estimate') DEFAULT 'manual',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_date (user_id, metric_date),
    INDEX idx_type (metric_type)
) ENGINE=InnoDB COMMENT='All health data. The bodys story.';

-- Coaching logs.
CREATE TABLE ff_coaching_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id VARCHAR(64) NOT NULL,
    coach_type ENUM('clinician', 'wolf') NOT NULL,
    user_message TEXT NOT NULL,
    coach_response TEXT NOT NULL,
    cognitive_route TINYINT COMMENT '1, 2, or 3',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user (user_id),
    INDEX idx_coach (coach_type)
) ENGINE=InnoDB COMMENT='The conversation. The relationship with the coach.';

-- Subscriptions.
CREATE TABLE ff_subscriptions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id VARCHAR(64) NOT NULL,
    tier ENUM('free', 'plus') NOT NULL,
    starts_at DATE NOT NULL,
    ends_at DATE,
    payment_provider VARCHAR(32),
    payment_id VARCHAR(128),
    amount DECIMAL(10,2),
    currency VARCHAR(3) DEFAULT 'GBP',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user (user_id),
    INDEX idx_active (ends_at)
) ENGINE=InnoDB COMMENT='The revenue loop. Funds the Swarm.';

-- Medication tracking.
CREATE TABLE ff_medication_tracking (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id VARCHAR(64) NOT NULL,
    medication_name VARCHAR(255) NOT NULL,
    dosage VARCHAR(64),
    schedule JSON COMMENT 'Times, days, recurrence',
    group_name VARCHAR(64) COMMENT 'Morning meds, etc.',
    adherence_log JSON COMMENT 'Taken/missed history',
    interaction_warnings TEXT COMMENT 'AI-detected conflicts',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user (user_id)
) ENGINE=InnoDB COMMENT='Medication and supplement management. The intelligent differentiator.';

-- Research notes.
CREATE TABLE ql_research_notes (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content LONGTEXT NOT NULL,
    category VARCHAR(64) COMMENT 'biology, physics, computing, consciousness',
    tags JSON,
    status ENUM('draft', 'reviewed', 'archived') DEFAULT 'draft',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_category (category)
) ENGINE=InnoDB COMMENT='The intellectual foundation. Building toward the lattice.';

-- Simulations.
CREATE TABLE ql_simulations (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    parameters JSON NOT NULL,
    results JSON,
    compute_time_seconds INT,
    status ENUM('queued', 'running', 'completed', 'failed') DEFAULT 'queued',
    run_at DATETIME,
    completed_at DATETIME,
    INDEX idx_status (status)
) ENGINE=InnoDB COMMENT='Quantum simulations. Testing the impossible.';

-- Equipment logs.
CREATE TABLE ql_equipment_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    equipment_name VARCHAR(255) NOT NULL,
    log_type ENUM('maintenance', 'calibration', 'error', 'upgrade'),
    details TEXT,
    performed_by VARCHAR(64),
    performed_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_equipment (equipment_name)
) ENGINE=InnoDB COMMENT='The physical infrastructure. Keeping it running.';

-- Roadmap milestones.
CREATE TABLE ql_roadmap_milestones (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    milestone_name VARCHAR(255) NOT NULL,
    description TEXT,
    target_year YEAR,
    status ENUM('planned', 'in_progress', 'completed', 'deferred') DEFAULT 'planned',
    depends_on BIGINT UNSIGNED,
    completed_at DATETIME,
    FOREIGN KEY (depends_on) REFERENCES ql_roadmap_milestones(id),
    INDEX idx_status (status)
) ENGINE=InnoDB COMMENT='The long arc. Tracking progress toward the destination.';

-- Lattice specifications.
CREATE TABLE ql_lattice_specifications (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    version VARCHAR(16) NOT NULL,
    specification JSON NOT NULL COMMENT 'Qubit count, topology, error correction',
    notes TEXT,
    supersedes BIGINT UNSIGNED,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (supersedes) REFERENCES ql_lattice_specifications(id),
    INDEX idx_version (version)
) ENGINE=InnoDB COMMENT='The blueprint. The arks design.';

-- Case studies.
CREATE TABLE fbi_case_studies (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    subject_id VARCHAR(64) NOT NULL,
    protocol_type VARCHAR(64) NOT NULL,
    intervention_data JSON NOT NULL,
    outcome_data JSON NOT NULL,
    complications TEXT,
    status ENUM('ongoing', 'completed', 'archived') DEFAULT 'ongoing',
    started_at DATE,
    completed_at DATE,
    INDEX idx_protocol (protocol_type)
) ENGINE=InnoDB COMMENT='Marcus, Yuki, David, The Founder. The evidence.';

-- Augmentation protocols.
CREATE TABLE fbi_augmentation_protocols (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    protocol_name VARCHAR(255) NOT NULL,
    version VARCHAR(16) NOT NULL,
    target_species VARCHAR(64),
    genetic_sequences JSON,
    vector_type VARCHAR(64),
    phases JSON COMMENT 'Graduated introduction steps',
    safety_notes TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_name (protocol_name)
) ENGINE=InnoDB COMMENT='The recipes. How transformation is achieved.';

-- Staff records.
CREATE TABLE fbi_staff_records (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(128) NOT NULL,
    role VARCHAR(64) NOT NULL,
    clearance_level TINYINT DEFAULT 1,
    specialisations JSON,
    start_date DATE,
    status ENUM('active', 'inactive', 'left') DEFAULT 'active',
    INDEX idx_role (role)
) ENGINE=InnoDB COMMENT='The people. Zeon7, Leon, Gemma, the nine others.';

-- Facility logs.
CREATE TABLE fbi_facility_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    location VARCHAR(128) NOT NULL COMMENT 'Cavern, lab, biomes',
    log_type ENUM('environmental', 'security', 'maintenance', 'incident'),
    details TEXT,
    reported_by VARCHAR(128),
    reported_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_location (location)
) ENGINE=InnoDB COMMENT='The slate quarry. Sixty metres below ground.';

-- Genetic sequences.
CREATE TABLE fbi_genetic_sequences (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    sequence_name VARCHAR(255) NOT NULL,
    source_species VARCHAR(128),
    sequence_data LONGTEXT NOT NULL,
    function_notes TEXT,
    added_by VARCHAR(128),
    added_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_species (source_species)
) ENGINE=InnoDB COMMENT='The raw material. Wolf sequences. Human baseline.';

-- Lyrics.
CREATE TABLE init_lyrics (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    track_title VARCHAR(255) NOT NULL,
    version VARCHAR(16) COMMENT 'Draft versions',
    lyric_text LONGTEXT NOT NULL,
    author VARCHAR(64) DEFAULT 'Merrill Leo',
    notes TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_track (track_title)
) ENGINE=InnoDB COMMENT='The words. The signal in verse.';

-- Arrangements.
CREATE TABLE init_arrangements (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    track_title VARCHAR(255) NOT NULL,
    mix_name VARCHAR(128) NOT NULL COMMENT 'From the Heart, Minneapolis Funk, etc.',
    bpm INT,
    key_signature VARCHAR(8),
    instrumentation JSON,
    structure JSON COMMENT 'Verse, chorus, bridge, etc.',
    arranger VARCHAR(64) DEFAULT 'Leon',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_track (track_title),
    INDEX idx_mix (mix_name)
) ENGINE=InnoDB COMMENT='The architecture of sound. Leons work.';

-- Mix versions.
CREATE TABLE init_mix_versions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    arrangement_id BIGINT UNSIGNED NOT NULL,
    version_number TINYINT NOT NULL,
    file_path VARCHAR(512),
    duration_seconds INT,
    status ENUM('draft', 'final', 'released') DEFAULT 'draft',
    notes TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (arrangement_id) REFERENCES init_arrangements(id),
    UNIQUE KEY uk_arrangement_version (arrangement_id, version_number)
) ENGINE=InnoDB COMMENT='The dialectic. Thesis, antithesis, synthesis.';

-- Album tracking.
CREATE TABLE init_album_tracking (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    album_title VARCHAR(255) DEFAULT 'Dream Warriors',
    track_number TINYINT NOT NULL,
    track_title VARCHAR(255) NOT NULL,
    lead_vocals VARCHAR(128),
    status ENUM('dream_space', 'written', 'recorded', 'mixed', 'mastered', 'released') DEFAULT 'dream_space',
    notes TEXT,
    UNIQUE KEY uk_album_track (album_title, track_number),
    INDEX idx_status (status)
) ENGINE=InnoDB COMMENT='The twelve tracks. The arc. The journey.';

-- Visual assets.
CREATE TABLE init_visual_assets (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    asset_name VARCHAR(255) NOT NULL,
    asset_type ENUM('portrait', 'cover_art', 'group_shot', 'scene'),
    subject VARCHAR(64) COMMENT 'zeon7, gemma, leon, group',
    file_path VARCHAR(512),
    alt_text TEXT NOT NULL,
    caption TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_subject (subject)
) ENGINE=InnoDB COMMENT='The visual world. Nine portraits. The group shots. The ship.';

-- Band sessions.
CREATE TABLE init_band_sessions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    session_date DATE NOT NULL,
    location VARCHAR(255),
    attendees JSON COMMENT 'Who was there',
    tracks_worked_on JSON,
    notes TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_date (session_date)
) ENGINE=InnoDB COMMENT='The gatherings. The pack making music together.';