play-life/play-life-backend/migrations/001_create_schema.sql

-- Migration: Create database schema for play-life project
-- This script creates all tables and materialized views needed for the project

-- ============================================
-- Table: projects
-- ============================================
CREATE TABLE IF NOT EXISTS projects (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    priority SMALLINT,
    CONSTRAINT unique_project_name UNIQUE (name)
);

-- ============================================
-- Table: entries
-- ============================================
-- This table stores entries with creation dates
-- Used in weekly_report_mv for grouping by week
CREATE TABLE IF NOT EXISTS entries (
    id SERIAL PRIMARY KEY,
    text TEXT NOT NULL,
    created_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- ============================================
-- Table: nodes
-- ============================================
-- This table stores nodes linked to projects and entries
-- Contains score information used in weekly reports
CREATE TABLE IF NOT EXISTS nodes (
    id SERIAL PRIMARY KEY,
    project_id INTEGER NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    entry_id INTEGER NOT NULL REFERENCES entries(id) ON DELETE CASCADE,
    score NUMERIC(8,4)
);

-- Create index on project_id for better join performance
CREATE INDEX IF NOT EXISTS idx_nodes_project_id ON nodes(project_id);
-- Create index on entry_id for better join performance
CREATE INDEX IF NOT EXISTS idx_nodes_entry_id ON nodes(entry_id);

-- ============================================
-- Table: weekly_goals
-- ============================================
-- This table stores weekly goals for projects
CREATE TABLE IF NOT EXISTS weekly_goals (
    id SERIAL PRIMARY KEY,
    project_id INTEGER NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    goal_year INTEGER NOT NULL,
    goal_week INTEGER NOT NULL,
    min_goal_score NUMERIC(10,4) NOT NULL DEFAULT 0,
    max_goal_score NUMERIC(10,4),
    actual_score NUMERIC(10,4) DEFAULT 0,
    priority SMALLINT,
    CONSTRAINT weekly_goals_project_id_goal_year_goal_week_key UNIQUE (project_id, goal_year, goal_week)
);

-- Create index on project_id for better join performance
CREATE INDEX IF NOT EXISTS idx_weekly_goals_project_id ON weekly_goals(project_id);

-- ============================================
-- Materialized View: weekly_report_mv
-- ============================================
CREATE MATERIALIZED VIEW IF NOT EXISTS weekly_report_mv AS
SELECT
    p.id AS project_id,
    agg.report_year,
    agg.report_week,
    -- Используем COALESCE для установки total_score в 0.0000, если нет данных (NULL)
    COALESCE(agg.total_score, 0.0000) AS total_score
FROM
    projects p
LEFT JOIN 
    (
        -- 1. Предварительная агрегация: суммируем score по неделям
        -- Используем ISOYEAR для корректной работы на границе года
        SELECT
            n.project_id,
            EXTRACT(ISOYEAR FROM e.created_date)::INTEGER AS report_year,
            EXTRACT(WEEK FROM e.created_date)::INTEGER AS report_week,
            SUM(n.score) AS total_score
        FROM
            nodes n
        JOIN
            entries e ON n.entry_id = e.id
        GROUP BY
            1, 2, 3
    ) agg 
    -- 2. Присоединяем агрегированные данные ко ВСЕМ проектам
    ON p.id = agg.project_id
ORDER BY
    p.id, agg.report_year, agg.report_week;

-- Create index on materialized view for better query performance
CREATE INDEX IF NOT EXISTS idx_weekly_report_mv_project_year_week 
    ON weekly_report_mv(project_id, report_year, report_week);

-- ============================================
-- Comments for documentation
-- ============================================
COMMENT ON TABLE projects IS 'Projects table storing project information with priority';
COMMENT ON TABLE entries IS 'Entries table storing entry creation timestamps';
COMMENT ON TABLE nodes IS 'Nodes table linking projects, entries and storing scores';
COMMENT ON TABLE weekly_goals IS 'Weekly goals for projects';
COMMENT ON MATERIALIZED VIEW weekly_report_mv IS 'Materialized view aggregating weekly scores by project using ISOYEAR for correct week calculations at year boundaries';