-- ============================================================
-- MIGRATION 016: ZOPK Milestones (Timeline)
-- ============================================================
-- Created: 2026-01-17
-- Purpose: Add milestones table for ZOPK timeline visualization
-- ============================================================

-- Milestones table for timeline
CREATE TABLE IF NOT EXISTS zopk_milestones (
    id SERIAL PRIMARY KEY,

    -- Basic info
    title VARCHAR(255) NOT NULL,
    description TEXT,

    -- Categorization (for UI grouping)
    category VARCHAR(50) NOT NULL DEFAULT 'other',
    -- Categories: nuclear, offshore, infrastructure, defense, other

    -- Project association (optional)
    project_id INTEGER REFERENCES zopk_projects(id) ON DELETE SET NULL,

    -- Timeline
    target_date DATE,                -- Planned/expected date
    actual_date DATE,                -- Actual completion date (if completed)
    date_precision VARCHAR(20) DEFAULT 'exact',  -- exact, month, quarter, year

    -- Status
    status VARCHAR(20) NOT NULL DEFAULT 'planned',
    -- Status: planned, in_progress, completed, delayed, cancelled

    -- Source linking
    source_news_id INTEGER REFERENCES zopk_news(id) ON DELETE SET NULL,
    source_fact_id INTEGER REFERENCES zopk_knowledge_facts(id) ON DELETE SET NULL,
    source_url VARCHAR(1000),        -- External source URL

    -- Display settings
    icon VARCHAR(50) DEFAULT '📌',
    color VARCHAR(7) DEFAULT '#059669',  -- HEX color
    is_featured BOOLEAN DEFAULT FALSE,
    display_order INTEGER DEFAULT 0,

    -- Verification
    is_verified BOOLEAN DEFAULT FALSE,
    verified_by INTEGER REFERENCES users(id),
    verified_at TIMESTAMP,

    -- Timestamps
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- Indexes
CREATE INDEX IF NOT EXISTS idx_milestones_project ON zopk_milestones(project_id);
CREATE INDEX IF NOT EXISTS idx_milestones_target_date ON zopk_milestones(target_date);
CREATE INDEX IF NOT EXISTS idx_milestones_status ON zopk_milestones(status);
CREATE INDEX IF NOT EXISTS idx_milestones_category ON zopk_milestones(category);
CREATE INDEX IF NOT EXISTS idx_milestones_featured ON zopk_milestones(is_featured) WHERE is_featured = TRUE;

-- Grant permissions
GRANT ALL ON TABLE zopk_milestones TO nordabiz_app;
GRANT USAGE, SELECT ON SEQUENCE zopk_milestones_id_seq TO nordabiz_app;

-- Insert sample milestones from known ZOPK events (all verified for display)
INSERT INTO zopk_milestones (title, description, category, target_date, status, icon, color, is_featured, is_verified, display_order) VALUES
    ('Podpisanie porozumienia MON-Kongsberg', 'Podpisanie porozumienia o współpracy między MON a Kongsberg Defence & Aerospace w zakresie budowy fabryki w Rumi', 'defense', '2024-03-15', 'completed', '📝', '#059669', TRUE, TRUE, 10),
    ('Pozwolenie środowiskowe Baltic Power', 'Uzyskanie pozwolenia środowiskowego dla morskiej farmy wiatrowej Baltic Power', 'offshore', '2025-06-15', 'completed', '📋', '#10b981', FALSE, TRUE, 40),
    ('Rozpoczęcie budowy fabryki Kongsberg w Rumi', 'Start prac budowlanych zakładu produkcji dronów morskich w Rumi Invest Park', 'defense', '2025-09-01', 'in_progress', '🏗️', '#f59e0b', TRUE, TRUE, 30),
    ('Decyzja lokalizacyjna elektrowni jądrowej', 'Wydanie decyzji lokalizacyjnej dla elektrowni jądrowej w Lubiatowie-Kopalino', 'nuclear', '2026-03-01', 'planned', '⚖️', '#3b82f6', TRUE, TRUE, 20),
    ('Uruchomienie pierwszego bloku jądrowego', 'Planowane uruchomienie pierwszego bloku elektrowni jądrowej w Lubiatowie', 'nuclear', '2033-12-01', 'planned', '⚡', '#8b5cf6', TRUE, TRUE, 100)
ON CONFLICT DO NOTHING;

-- Comment
COMMENT ON TABLE zopk_milestones IS 'Timeline milestones for ZOPK projects - used for public timeline visualization';
