db_schema.sql

-- Generated from alembic/versions/001_initial_schema.py
-- Source of truth: Alembic revision 001_initial_schema

CREATE OR REPLACE FUNCTION date_trunc_immutable(timestamp with time zone)
        RETURNS date AS $$
            SELECT $1::date;
        $$ LANGUAGE SQL IMMUTABLE;

CREATE TABLE IF NOT EXISTS tariffs (
            id SERIAL PRIMARY KEY,
            name VARCHAR(50) NOT NULL UNIQUE,
            slug VARCHAR(50) NOT NULL UNIQUE,

            daily_limit INTEGER DEFAULT NULL,
            monthly_limit INTEGER DEFAULT NULL,
            max_url_length INTEGER NOT NULL DEFAULT 2048,
            max_dynamic_qr INTEGER DEFAULT NULL,

            allowed_formats TEXT[] DEFAULT ARRAY['png', 'svg'],
            history_retention_days INTEGER DEFAULT 30,
            analytics_retention_days INTEGER DEFAULT 30,
            priority_support BOOLEAN DEFAULT FALSE,
            custom_branding BOOLEAN DEFAULT FALSE,

            price_monthly INTEGER DEFAULT 0,

            is_active BOOLEAN DEFAULT TRUE,
            created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
        );

CREATE INDEX IF NOT EXISTS idx_tariffs_slug ON tariffs(slug);

CREATE INDEX IF NOT EXISTS idx_tariffs_active ON tariffs(is_active);

INSERT INTO tariffs (
            name,
            slug,
            daily_limit,
            monthly_limit,
            price_monthly,
            allowed_formats,
            history_retention_days,
            max_dynamic_qr,
            analytics_retention_days
        )
        VALUES
            ('Trial', 'trial', 100, NULL, 0, ARRAY['png', 'svg'], 365, 10, 365),
            ('Freemium', 'freemium', 3, 90, 0, ARRAY['png'], 7, 0, 7),
            ('Basic', 'basic', 20, 600, 499, ARRAY['png', 'svg'], 30, 5, 30),
            ('Premium', 'premium', 100, NULL, 999, ARRAY['png', 'svg'], 365, NULL, 365),
            ('Enterprise', 'enterprise', NULL, NULL, 2999, ARRAY['png', 'svg'], 365, NULL, 365)
        ON CONFLICT (slug) DO NOTHING;

CREATE TABLE IF NOT EXISTS users (
            telegram_id BIGINT PRIMARY KEY,

            language_code VARCHAR(10) NOT NULL DEFAULT 'en',
            username VARCHAR(255),
            first_name VARCHAR(255),
            last_name VARCHAR(255),

            tariff_id INTEGER NOT NULL DEFAULT 1 REFERENCES tariffs(id),
            tariff_expires_at TIMESTAMPTZ,

            total_qr_generated INTEGER NOT NULL DEFAULT 0,
            last_qr_generated_at TIMESTAMPTZ,
            total_qr_deleted INTEGER NOT NULL DEFAULT 0,
            last_qr_deleted_at TIMESTAMPTZ,

            is_active BOOLEAN NOT NULL DEFAULT TRUE,
            is_banned BOOLEAN NOT NULL DEFAULT FALSE,

            created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
        );

CREATE INDEX IF NOT EXISTS idx_users_active_tariff
        ON users (tariff_id, last_qr_generated_at DESC)
        WHERE is_active = TRUE AND is_banned = FALSE;

CREATE INDEX IF NOT EXISTS idx_users_recently_active
        ON users (last_qr_generated_at DESC)
        INCLUDE (telegram_id, language_code, tariff_id)
        WHERE is_active = TRUE AND last_qr_generated_at IS NOT NULL;

CREATE INDEX IF NOT EXISTS idx_users_tariff ON users(tariff_id);

CREATE INDEX IF NOT EXISTS idx_users_active ON users(is_active);

CREATE TABLE IF NOT EXISTS qr_codes (
            id SERIAL PRIMARY KEY,
            user_id BIGINT NOT NULL REFERENCES users(telegram_id) ON DELETE CASCADE,

            url TEXT NOT NULL,
            url_hash VARCHAR(64) NOT NULL,
            format VARCHAR(10) NOT NULL DEFAULT 'png',
            title VARCHAR(255) DEFAULT NULL,

            file_size_bytes INTEGER,
            generation_time_ms INTEGER,
            telegram_message_id BIGINT,

            is_dynamic BOOLEAN DEFAULT FALSE,
            short_code VARCHAR(20) UNIQUE,

            deleted_at TIMESTAMPTZ DEFAULT NULL,
            created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
        );

CREATE INDEX IF NOT EXISTS idx_qr_user_dashboard
        ON qr_codes (user_id, is_dynamic, deleted_at)
        INCLUDE (id, url, format, created_at, short_code)
        WHERE deleted_at IS NULL;

CREATE INDEX IF NOT EXISTS idx_qr_user_history_active
        ON qr_codes (user_id, created_at DESC)
        INCLUDE (id, url, format, title)
        WHERE deleted_at IS NULL;

CREATE INDEX IF NOT EXISTS idx_qr_created ON qr_codes(created_at DESC);

CREATE INDEX IF NOT EXISTS idx_qr_short_code ON qr_codes(short_code) WHERE short_code IS NOT NULL;

CREATE INDEX IF NOT EXISTS idx_qr_deleted ON qr_codes(deleted_at) WHERE deleted_at IS NOT NULL;

CREATE TABLE IF NOT EXISTS qr_redirects (
            id BIGSERIAL PRIMARY KEY,
            qr_code_id BIGINT NOT NULL UNIQUE REFERENCES qr_codes(id) ON DELETE CASCADE,
            short_code VARCHAR(12) NOT NULL UNIQUE,

            original_url TEXT NOT NULL,
            target_url TEXT NOT NULL,

            clicks_count INTEGER DEFAULT 0,
            unique_clicks_count INTEGER DEFAULT 0,
            last_clicked_at TIMESTAMPTZ,

            is_active BOOLEAN DEFAULT TRUE,
            created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
        );

CREATE INDEX IF NOT EXISTS idx_redirects_lookup
        ON qr_redirects (short_code)
        INCLUDE (id, qr_code_id, target_url, is_active, clicks_count);

CREATE INDEX IF NOT EXISTS idx_redirects_user_dashboard
        ON qr_redirects (qr_code_id, is_active, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_redirects_qr_id ON qr_redirects(qr_code_id);

CREATE INDEX IF NOT EXISTS idx_redirects_active ON qr_redirects(is_active);

CREATE TABLE IF NOT EXISTS qr_url_changes (
            id BIGSERIAL PRIMARY KEY,
            qr_redirect_id BIGINT NOT NULL REFERENCES qr_redirects(id) ON DELETE CASCADE,

            old_url TEXT NOT NULL,
            new_url TEXT NOT NULL,

            changed_by BIGINT NOT NULL REFERENCES users(telegram_id),
            changed_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,

            change_reason VARCHAR(255)
        );

CREATE INDEX IF NOT EXISTS idx_url_changes_redirect ON qr_url_changes(qr_redirect_id, changed_at DESC);

CREATE INDEX IF NOT EXISTS idx_url_changes_user ON qr_url_changes(changed_by);

CREATE TABLE IF NOT EXISTS qr_click_analytics (
            id BIGSERIAL,
            qr_redirect_id BIGINT NOT NULL REFERENCES qr_redirects(id) ON DELETE CASCADE,

            clicked_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,

            user_agent TEXT,
            ip_address INET,
            country_code VARCHAR(2),
            city VARCHAR(100),
            referer TEXT,
            device_type VARCHAR(20),
            os VARCHAR(50),
            browser VARCHAR(50),

            PRIMARY KEY (id, clicked_at)
        ) PARTITION BY RANGE (clicked_at);

CREATE TABLE IF NOT EXISTS qr_click_analytics_2026_01
            PARTITION OF qr_click_analytics
            FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

CREATE TABLE IF NOT EXISTS qr_click_analytics_2026_02
            PARTITION OF qr_click_analytics
            FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

CREATE TABLE IF NOT EXISTS qr_click_analytics_2026_03
            PARTITION OF qr_click_analytics
            FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');

CREATE TABLE IF NOT EXISTS qr_click_analytics_2026_04
            PARTITION OF qr_click_analytics
            FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');

CREATE TABLE IF NOT EXISTS qr_click_analytics_2026_05
            PARTITION OF qr_click_analytics
            FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');

CREATE TABLE IF NOT EXISTS qr_click_analytics_2026_06
            PARTITION OF qr_click_analytics
            FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');

CREATE TABLE IF NOT EXISTS qr_click_analytics_2026_07
            PARTITION OF qr_click_analytics
            FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');

CREATE TABLE IF NOT EXISTS qr_click_analytics_2026_08
            PARTITION OF qr_click_analytics
            FOR VALUES FROM ('2026-08-01') TO ('2026-09-01');

CREATE TABLE IF NOT EXISTS qr_click_analytics_2026_09
            PARTITION OF qr_click_analytics
            FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');

CREATE TABLE IF NOT EXISTS qr_click_analytics_2026_10
            PARTITION OF qr_click_analytics
            FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');

CREATE TABLE IF NOT EXISTS qr_click_analytics_2026_11
            PARTITION OF qr_click_analytics
            FOR VALUES FROM ('2026-11-01') TO ('2026-12-01');

CREATE TABLE IF NOT EXISTS qr_click_analytics_2026_12
            PARTITION OF qr_click_analytics
            FOR VALUES FROM ('2026-12-01') TO ('2027-01-01');

CREATE TABLE IF NOT EXISTS qr_click_analytics_2027_01
            PARTITION OF qr_click_analytics
            FOR VALUES FROM ('2027-01-01') TO ('2027-02-01');

CREATE TABLE IF NOT EXISTS qr_click_analytics_2027_02
            PARTITION OF qr_click_analytics
            FOR VALUES FROM ('2027-02-01') TO ('2027-03-01');

CREATE TABLE IF NOT EXISTS qr_click_analytics_2027_03
            PARTITION OF qr_click_analytics
            FOR VALUES FROM ('2027-03-01') TO ('2027-04-01');

CREATE TABLE IF NOT EXISTS qr_click_analytics_2027_04
            PARTITION OF qr_click_analytics
            FOR VALUES FROM ('2027-04-01') TO ('2027-05-01');

CREATE TABLE IF NOT EXISTS qr_click_analytics_2027_05
            PARTITION OF qr_click_analytics
            FOR VALUES FROM ('2027-05-01') TO ('2027-06-01');

CREATE TABLE IF NOT EXISTS qr_click_analytics_2027_06
            PARTITION OF qr_click_analytics
            FOR VALUES FROM ('2027-06-01') TO ('2027-07-01');

CREATE TABLE IF NOT EXISTS qr_click_analytics_2027_07
            PARTITION OF qr_click_analytics
            FOR VALUES FROM ('2027-07-01') TO ('2027-08-01');

CREATE TABLE IF NOT EXISTS qr_click_analytics_2027_08
            PARTITION OF qr_click_analytics
            FOR VALUES FROM ('2027-08-01') TO ('2027-09-01');

CREATE TABLE IF NOT EXISTS qr_click_analytics_2027_09
            PARTITION OF qr_click_analytics
            FOR VALUES FROM ('2027-09-01') TO ('2027-10-01');

CREATE TABLE IF NOT EXISTS qr_click_analytics_2027_10
            PARTITION OF qr_click_analytics
            FOR VALUES FROM ('2027-10-01') TO ('2027-11-01');

CREATE TABLE IF NOT EXISTS qr_click_analytics_2027_11
            PARTITION OF qr_click_analytics
            FOR VALUES FROM ('2027-11-01') TO ('2027-12-01');

CREATE TABLE IF NOT EXISTS qr_click_analytics_2027_12
            PARTITION OF qr_click_analytics
            FOR VALUES FROM ('2027-12-01') TO ('2028-01-01');

CREATE INDEX IF NOT EXISTS idx_click_analytics_redirect_time ON qr_click_analytics(qr_redirect_id, clicked_at DESC);

CREATE INDEX IF NOT EXISTS idx_click_analytics_device_agg
        ON qr_click_analytics (qr_redirect_id, device_type)
        WHERE device_type IS NOT NULL;

CREATE INDEX IF NOT EXISTS idx_click_analytics_country_agg
        ON qr_click_analytics (qr_redirect_id, country_code)
        WHERE country_code IS NOT NULL;

CREATE INDEX IF NOT EXISTS idx_click_analytics_time_series
        ON qr_click_analytics (qr_redirect_id, date_trunc_immutable(clicked_at));

CREATE INDEX IF NOT EXISTS idx_click_analytics_unique_check
        ON qr_click_analytics (qr_redirect_id, ip_address, date_trunc_immutable(clicked_at));

CREATE INDEX IF NOT EXISTS idx_click_analytics_ip ON qr_click_analytics(ip_address);

CREATE INDEX IF NOT EXISTS idx_click_analytics_geo_backfill
        ON qr_click_analytics (ip_address, clicked_at)
        WHERE ip_address IS NOT NULL AND country_code IS NULL;

CREATE INDEX IF NOT EXISTS idx_click_analytics_country_city
        ON qr_click_analytics (qr_redirect_id, country_code, city)
        WHERE country_code IS NOT NULL;

CREATE TABLE IF NOT EXISTS daily_usage (
            user_id BIGINT NOT NULL REFERENCES users(telegram_id) ON DELETE CASCADE,
            usage_date DATE NOT NULL,
            qr_count INTEGER NOT NULL DEFAULT 0 CHECK (qr_count >= 0),

            created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,

            PRIMARY KEY (user_id, usage_date)
        ) PARTITION BY RANGE (usage_date);

CREATE TABLE IF NOT EXISTS daily_usage_2026_01
            PARTITION OF daily_usage
            FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

CREATE TABLE IF NOT EXISTS daily_usage_2026_02
            PARTITION OF daily_usage
            FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

CREATE TABLE IF NOT EXISTS daily_usage_2026_03
            PARTITION OF daily_usage
            FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');

CREATE TABLE IF NOT EXISTS daily_usage_2026_04
            PARTITION OF daily_usage
            FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');

CREATE TABLE IF NOT EXISTS daily_usage_2026_05
            PARTITION OF daily_usage
            FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');

CREATE TABLE IF NOT EXISTS daily_usage_2026_06
            PARTITION OF daily_usage
            FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');

CREATE TABLE IF NOT EXISTS daily_usage_2026_07
            PARTITION OF daily_usage
            FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');

CREATE TABLE IF NOT EXISTS daily_usage_2026_08
            PARTITION OF daily_usage
            FOR VALUES FROM ('2026-08-01') TO ('2026-09-01');

CREATE TABLE IF NOT EXISTS daily_usage_2026_09
            PARTITION OF daily_usage
            FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');

CREATE TABLE IF NOT EXISTS daily_usage_2026_10
            PARTITION OF daily_usage
            FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');

CREATE TABLE IF NOT EXISTS daily_usage_2026_11
            PARTITION OF daily_usage
            FOR VALUES FROM ('2026-11-01') TO ('2026-12-01');

CREATE TABLE IF NOT EXISTS daily_usage_2026_12
            PARTITION OF daily_usage
            FOR VALUES FROM ('2026-12-01') TO ('2027-01-01');

CREATE TABLE IF NOT EXISTS daily_usage_2027_01
            PARTITION OF daily_usage
            FOR VALUES FROM ('2027-01-01') TO ('2027-02-01');

CREATE TABLE IF NOT EXISTS daily_usage_2027_02
            PARTITION OF daily_usage
            FOR VALUES FROM ('2027-02-01') TO ('2027-03-01');

CREATE TABLE IF NOT EXISTS daily_usage_2027_03
            PARTITION OF daily_usage
            FOR VALUES FROM ('2027-03-01') TO ('2027-04-01');

CREATE TABLE IF NOT EXISTS daily_usage_2027_04
            PARTITION OF daily_usage
            FOR VALUES FROM ('2027-04-01') TO ('2027-05-01');

CREATE TABLE IF NOT EXISTS daily_usage_2027_05
            PARTITION OF daily_usage
            FOR VALUES FROM ('2027-05-01') TO ('2027-06-01');

CREATE TABLE IF NOT EXISTS daily_usage_2027_06
            PARTITION OF daily_usage
            FOR VALUES FROM ('2027-06-01') TO ('2027-07-01');

CREATE TABLE IF NOT EXISTS daily_usage_2027_07
            PARTITION OF daily_usage
            FOR VALUES FROM ('2027-07-01') TO ('2027-08-01');

CREATE TABLE IF NOT EXISTS daily_usage_2027_08
            PARTITION OF daily_usage
            FOR VALUES FROM ('2027-08-01') TO ('2027-09-01');

CREATE TABLE IF NOT EXISTS daily_usage_2027_09
            PARTITION OF daily_usage
            FOR VALUES FROM ('2027-09-01') TO ('2027-10-01');

CREATE TABLE IF NOT EXISTS daily_usage_2027_10
            PARTITION OF daily_usage
            FOR VALUES FROM ('2027-10-01') TO ('2027-11-01');

CREATE TABLE IF NOT EXISTS daily_usage_2027_11
            PARTITION OF daily_usage
            FOR VALUES FROM ('2027-11-01') TO ('2027-12-01');

CREATE TABLE IF NOT EXISTS daily_usage_2027_12
            PARTITION OF daily_usage
            FOR VALUES FROM ('2027-12-01') TO ('2028-01-01');

CREATE INDEX IF NOT EXISTS idx_daily_usage_limit_check
        ON daily_usage (user_id, usage_date)
        INCLUDE (qr_count);

CREATE INDEX IF NOT EXISTS idx_daily_usage_date ON daily_usage(usage_date DESC);

CREATE TABLE IF NOT EXISTS subscription_history (
            id SERIAL PRIMARY KEY,
            user_id BIGINT NOT NULL REFERENCES users(telegram_id) ON DELETE CASCADE,
            tariff_id INTEGER NOT NULL REFERENCES tariffs(id),

            start_date TIMESTAMPTZ NOT NULL,
            end_date TIMESTAMPTZ,

            price_paid INTEGER,
            payment_method VARCHAR(50),

            created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
        );

CREATE INDEX IF NOT EXISTS idx_subscription_user ON subscription_history(user_id, start_date DESC);

CREATE INDEX IF NOT EXISTS idx_subscription_tariff ON subscription_history(tariff_id);

CREATE OR REPLACE FUNCTION update_updated_at_column()
        RETURNS TRIGGER AS $$
        BEGIN
            NEW.updated_at = CURRENT_TIMESTAMP;
            RETURN NEW;
        END;
        $$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS update_users_updated_at ON users;

CREATE TRIGGER update_users_updated_at
        BEFORE UPDATE ON users
        FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

DROP TRIGGER IF EXISTS update_tariffs_updated_at ON tariffs;

CREATE TRIGGER update_tariffs_updated_at
        BEFORE UPDATE ON tariffs
        FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

DROP TRIGGER IF EXISTS update_redirects_updated_at ON qr_redirects;

CREATE TRIGGER update_redirects_updated_at
        BEFORE UPDATE ON qr_redirects
        FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TABLE IF NOT EXISTS feature_flags (
            id SERIAL PRIMARY KEY,

            name VARCHAR(100) NOT NULL UNIQUE,
            description TEXT NOT NULL DEFAULT '',

            status VARCHAR(20) NOT NULL DEFAULT 'disabled'
                CHECK (status IN ('disabled', 'enabled', 'percentage', 'users', 'segments')),

            rollout_percentage INTEGER DEFAULT 0
                CHECK (rollout_percentage >= 0 AND rollout_percentage <= 100),
            rollout_salt VARCHAR(50),

            enabled_users BIGINT[] DEFAULT ARRAY[]::BIGINT[],
            disabled_users BIGINT[] DEFAULT ARRAY[]::BIGINT[],
            enabled_segments TEXT[] DEFAULT ARRAY[]::TEXT[],

            metadata JSONB DEFAULT '{}',

            created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
            created_by BIGINT REFERENCES users(telegram_id),
            updated_by BIGINT REFERENCES users(telegram_id)
        );

CREATE INDEX IF NOT EXISTS idx_ff_name ON feature_flags(name);

CREATE INDEX IF NOT EXISTS idx_ff_status ON feature_flags(status);

CREATE INDEX IF NOT EXISTS idx_ff_updated ON feature_flags(updated_at DESC);

DROP TRIGGER IF EXISTS update_feature_flags_updated_at ON feature_flags;

CREATE TRIGGER update_feature_flags_updated_at
        BEFORE UPDATE ON feature_flags
        FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TABLE IF NOT EXISTS feature_flag_history (
            id BIGSERIAL PRIMARY KEY,

            flag_id INTEGER NOT NULL,
            flag_name VARCHAR(100) NOT NULL,

            action VARCHAR(20) NOT NULL
                CHECK (action IN ('created', 'updated', 'deleted')),

            old_value JSONB,
            new_value JSONB,

            changed_by BIGINT NOT NULL REFERENCES users(telegram_id),
            changed_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,

            change_reason TEXT
        );

CREATE INDEX IF NOT EXISTS idx_ffh_flag_time ON feature_flag_history(flag_id, changed_at DESC);

CREATE INDEX IF NOT EXISTS idx_ffh_name_time ON feature_flag_history(flag_name, changed_at DESC);

CREATE INDEX IF NOT EXISTS idx_ffh_user ON feature_flag_history(changed_by);

CREATE INDEX IF NOT EXISTS idx_ffh_action ON feature_flag_history(action);

CREATE TABLE IF NOT EXISTS event_dedup (
            event_id UUID PRIMARY KEY,
            created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
        );

CREATE INDEX IF NOT EXISTS idx_event_dedup_created_at ON event_dedup(created_at DESC);

CREATE TABLE IF NOT EXISTS domain_events (
            id BIGSERIAL,
            event_id UUID NOT NULL,

            event_name VARCHAR(100) NOT NULL,
            event_version VARCHAR(20) NOT NULL DEFAULT '1.0.0',
            aggregate_type VARCHAR(50) NOT NULL,
            aggregate_id BIGINT NOT NULL,

            payload JSONB NOT NULL,

            occurred_at TIMESTAMPTZ NOT NULL,
            stored_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,

            processed BOOLEAN DEFAULT TRUE,
            processed_at TIMESTAMPTZ,
            processing_error TEXT,

            PRIMARY KEY (id, occurred_at),
            UNIQUE (event_id, occurred_at)
        ) PARTITION BY RANGE (occurred_at);

CREATE TABLE IF NOT EXISTS domain_events_2026_01
            PARTITION OF domain_events
            FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

CREATE TABLE IF NOT EXISTS domain_events_2026_02
            PARTITION OF domain_events
            FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

CREATE TABLE IF NOT EXISTS domain_events_2026_03
            PARTITION OF domain_events
            FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');

CREATE TABLE IF NOT EXISTS domain_events_2026_04
            PARTITION OF domain_events
            FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');

CREATE TABLE IF NOT EXISTS domain_events_2026_05
            PARTITION OF domain_events
            FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');

CREATE TABLE IF NOT EXISTS domain_events_2026_06
            PARTITION OF domain_events
            FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');

CREATE TABLE IF NOT EXISTS domain_events_2026_07
            PARTITION OF domain_events
            FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');

CREATE TABLE IF NOT EXISTS domain_events_2026_08
            PARTITION OF domain_events
            FOR VALUES FROM ('2026-08-01') TO ('2026-09-01');

CREATE TABLE IF NOT EXISTS domain_events_2026_09
            PARTITION OF domain_events
            FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');

CREATE TABLE IF NOT EXISTS domain_events_2026_10
            PARTITION OF domain_events
            FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');

CREATE TABLE IF NOT EXISTS domain_events_2026_11
            PARTITION OF domain_events
            FOR VALUES FROM ('2026-11-01') TO ('2026-12-01');

CREATE TABLE IF NOT EXISTS domain_events_2026_12
            PARTITION OF domain_events
            FOR VALUES FROM ('2026-12-01') TO ('2027-01-01');

CREATE TABLE IF NOT EXISTS domain_events_2027_01
            PARTITION OF domain_events
            FOR VALUES FROM ('2027-01-01') TO ('2027-02-01');

CREATE TABLE IF NOT EXISTS domain_events_2027_02
            PARTITION OF domain_events
            FOR VALUES FROM ('2027-02-01') TO ('2027-03-01');

CREATE TABLE IF NOT EXISTS domain_events_2027_03
            PARTITION OF domain_events
            FOR VALUES FROM ('2027-03-01') TO ('2027-04-01');

CREATE TABLE IF NOT EXISTS domain_events_2027_04
            PARTITION OF domain_events
            FOR VALUES FROM ('2027-04-01') TO ('2027-05-01');

CREATE TABLE IF NOT EXISTS domain_events_2027_05
            PARTITION OF domain_events
            FOR VALUES FROM ('2027-05-01') TO ('2027-06-01');

CREATE TABLE IF NOT EXISTS domain_events_2027_06
            PARTITION OF domain_events
            FOR VALUES FROM ('2027-06-01') TO ('2027-07-01');

CREATE TABLE IF NOT EXISTS domain_events_2027_07
            PARTITION OF domain_events
            FOR VALUES FROM ('2027-07-01') TO ('2027-08-01');

CREATE TABLE IF NOT EXISTS domain_events_2027_08
            PARTITION OF domain_events
            FOR VALUES FROM ('2027-08-01') TO ('2027-09-01');

CREATE TABLE IF NOT EXISTS domain_events_2027_09
            PARTITION OF domain_events
            FOR VALUES FROM ('2027-09-01') TO ('2027-10-01');

CREATE TABLE IF NOT EXISTS domain_events_2027_10
            PARTITION OF domain_events
            FOR VALUES FROM ('2027-10-01') TO ('2027-11-01');

CREATE TABLE IF NOT EXISTS domain_events_2027_11
            PARTITION OF domain_events
            FOR VALUES FROM ('2027-11-01') TO ('2027-12-01');

CREATE TABLE IF NOT EXISTS domain_events_2027_12
            PARTITION OF domain_events
            FOR VALUES FROM ('2027-12-01') TO ('2028-01-01');

CREATE INDEX IF NOT EXISTS idx_de_aggregate ON domain_events(aggregate_type, aggregate_id, id);

CREATE INDEX IF NOT EXISTS idx_de_aggregate_fast_replay
        ON domain_events (aggregate_type, aggregate_id, occurred_at)
        INCLUDE (event_name, event_version)
        WHERE processed = TRUE;

CREATE INDEX IF NOT EXISTS idx_de_analytics
        ON domain_events (event_name, occurred_at)
        INCLUDE (aggregate_id, payload);

CREATE INDEX IF NOT EXISTS idx_de_event_name ON domain_events(event_name, occurred_at DESC);

CREATE INDEX IF NOT EXISTS idx_de_occurred_at ON domain_events(occurred_at DESC);

CREATE INDEX IF NOT EXISTS idx_de_unprocessed ON domain_events(processed) WHERE processed = FALSE;

CREATE INDEX IF NOT EXISTS idx_de_event_id ON domain_events(event_id);

ALTER TABLE qr_codes SET (
            autovacuum_vacuum_scale_factor = 0.1,
            autovacuum_analyze_scale_factor = 0.05
        );

DO $$
        BEGIN
            EXECUTE 'CREATE EXTENSION IF NOT EXISTS pg_stat_statements';
        EXCEPTION
            WHEN insufficient_privilege THEN
                RAISE NOTICE 'Skipping extension pg_stat_statements: insufficient privileges';
        END
        $$;

DO $$
        BEGIN
            EXECUTE 'CREATE EXTENSION IF NOT EXISTS pgstattuple';
        EXCEPTION
            WHEN insufficient_privilege THEN
                RAISE NOTICE 'Skipping extension pgstattuple: insufficient privileges';
        END
        $$;

CREATE OR REPLACE FUNCTION create_monthly_partitions()
        RETURNS void AS $$
        DECLARE
            table_name TEXT;
            partition_name TEXT;
            start_date DATE;
            end_date DATE;
        BEGIN
            start_date := date_trunc('month', CURRENT_DATE + INTERVAL '1 month');
            end_date := start_date + INTERVAL '1 month';

            FOREACH table_name IN ARRAY ARRAY[
                'qr_click_analytics',
                'daily_usage',
                'domain_events'
            ] LOOP
                partition_name := table_name || '_' || to_char(start_date, 'YYYY_MM');

                IF NOT EXISTS (
                    SELECT 1
                    FROM pg_class
                    WHERE relname = partition_name
                ) THEN
                    EXECUTE format(
                        'CREATE TABLE %I PARTITION OF %I FOR VALUES FROM (%L) TO (%L)',
                        partition_name,
                        table_name,
                        start_date,
                        end_date
                    );
                    RAISE NOTICE 'Created partition: %', partition_name;
                END IF;
            END LOOP;
        END;
        $$ LANGUAGE plpgsql;

COMMENT ON FUNCTION create_monthly_partitions() IS
        'Automatically creates next month partition for qr_click_analytics, daily_usage, and domain_events tables.
        Schedule this function to run monthly using pg_cron or external scheduler:
        Example: SELECT cron.schedule(''create-partitions'', ''0 0 25 * *'', ''SELECT create_monthly_partitions()'');';