CREATE TABLE savings_pocket_categories (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id uuid REFERENCES users(id) ON DELETE CASCADE,
    name text NOT NULL,
    icon text NOT NULL DEFAULT 'piggy-bank',
    color text NOT NULL DEFAULT '#2563EB',
    status text NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'disabled')),
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    CHECK (icon ~ '^[a-z0-9_-]{1,40}$'),
    CHECK (color ~ '^#[0-9A-Fa-f]{6}$'),
    UNIQUE (user_id, name)
);

CREATE UNIQUE INDEX savings_pocket_categories_global_name_uniq
    ON savings_pocket_categories (name)
    WHERE user_id IS NULL;

CREATE INDEX savings_pocket_categories_user_status_idx
    ON savings_pocket_categories (user_id, status, name);

CREATE TRIGGER savings_pocket_categories_set_updated_at
BEFORE UPDATE ON savings_pocket_categories
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

ALTER TABLE savings_goals
    ADD COLUMN IF NOT EXISTS category_id uuid REFERENCES savings_pocket_categories(id) ON DELETE SET NULL,
    ADD COLUMN IF NOT EXISTS icon text NOT NULL DEFAULT '',
    ADD COLUMN IF NOT EXISTS color text NOT NULL DEFAULT '',
    ADD COLUMN IF NOT EXISTS pocket_type text NOT NULL DEFAULT 'savings_pocket' CHECK (pocket_type IN ('savings_goal', 'savings_pocket', 'vault')),
    ADD COLUMN IF NOT EXISTS visibility text NOT NULL DEFAULT 'private' CHECK (visibility IN ('private', 'shared')),
    ADD COLUMN IF NOT EXISTS provider_name text NOT NULL DEFAULT '',
    ADD COLUMN IF NOT EXISTS external_pocket_id text NOT NULL DEFAULT '',
    ADD COLUMN IF NOT EXISTS provider_sync_status text NOT NULL DEFAULT 'not_configured' CHECK (provider_sync_status IN ('not_configured', 'pending', 'synced', 'failed', 'disabled')),
    ADD COLUMN IF NOT EXISTS provider_sync_error text NOT NULL DEFAULT '',
    ADD COLUMN IF NOT EXISTS provider_synced_at timestamptz,
    ADD CONSTRAINT savings_goals_icon_check CHECK (icon = '' OR icon ~ '^[a-z0-9_-]{1,40}$'),
    ADD CONSTRAINT savings_goals_color_check CHECK (color = '' OR color ~ '^#[0-9A-Fa-f]{6}$'),
    ADD CONSTRAINT savings_goals_provider_link_check CHECK (
        (provider_sync_status = 'not_configured' AND provider_name = '' AND external_pocket_id = '')
        OR provider_name <> ''
    );

CREATE INDEX IF NOT EXISTS savings_goals_category_idx
    ON savings_goals (category_id, status, created_at DESC);

CREATE INDEX IF NOT EXISTS savings_goals_provider_sync_idx
    ON savings_goals (provider_name, provider_sync_status, updated_at DESC)
    WHERE provider_name <> '';

CREATE TABLE savings_pocket_roundup_rules (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    savings_goal_id uuid NOT NULL REFERENCES savings_goals(id) ON DELETE CASCADE,
    user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    source text NOT NULL DEFAULT 'card_payments' CHECK (source IN ('card_payments', 'account_transactions', 'all_money_out')),
    round_to_increment_cents bigint NOT NULL DEFAULT 100 CHECK (round_to_increment_cents > 0),
    multiplier numeric(12, 4) NOT NULL DEFAULT 1 CHECK (multiplier > 0),
    minimum_roundup_cents bigint NOT NULL DEFAULT 1 CHECK (minimum_roundup_cents > 0),
    maximum_roundup_cents bigint NOT NULL DEFAULT 500 CHECK (maximum_roundup_cents >= minimum_roundup_cents),
    monthly_cap_cents bigint CHECK (monthly_cap_cents IS NULL OR monthly_cap_cents > 0),
    status text NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'paused', 'disabled')),
    last_applied_at timestamptz,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    UNIQUE (savings_goal_id, source)
);

CREATE INDEX savings_pocket_roundup_rules_user_status_idx
    ON savings_pocket_roundup_rules (user_id, status, updated_at DESC);

CREATE TRIGGER savings_pocket_roundup_rules_set_updated_at
BEFORE UPDATE ON savings_pocket_roundup_rules
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE TABLE savings_pocket_recurring_rules (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    savings_goal_id uuid NOT NULL REFERENCES savings_goals(id) ON DELETE CASCADE,
    user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    amount_cents bigint NOT NULL CHECK (amount_cents > 0),
    currency char(3) NOT NULL REFERENCES currencies(code) ON DELETE RESTRICT,
    frequency text NOT NULL CHECK (frequency IN ('weekly', 'monthly')),
    interval_count integer NOT NULL DEFAULT 1 CHECK (interval_count BETWEEN 1 AND 12),
    day_of_month integer CHECK (day_of_month BETWEEN 1 AND 28),
    day_of_week integer CHECK (day_of_week BETWEEN 1 AND 7),
    next_run_on date NOT NULL,
    description text NOT NULL DEFAULT '',
    status text NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'paused', 'canceled')),
    last_run_at timestamptz,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    CHECK (
        (frequency = 'monthly' AND day_of_month IS NOT NULL AND day_of_week IS NULL)
        OR (frequency = 'weekly' AND day_of_week IS NOT NULL AND day_of_month IS NULL)
    )
);

CREATE INDEX savings_pocket_recurring_rules_due_idx
    ON savings_pocket_recurring_rules (status, next_run_on);

CREATE INDEX savings_pocket_recurring_rules_goal_idx
    ON savings_pocket_recurring_rules (savings_goal_id, status, created_at DESC);

CREATE TRIGGER savings_pocket_recurring_rules_set_updated_at
BEFORE UPDATE ON savings_pocket_recurring_rules
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE TABLE savings_pocket_members (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    savings_goal_id uuid NOT NULL REFERENCES savings_goals(id) ON DELETE CASCADE,
    owner_user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    member_user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    role text NOT NULL CHECK (role IN ('co_owner', 'contributor', 'viewer')),
    status text NOT NULL DEFAULT 'active' CHECK (status IN ('invited', 'active', 'revoked')),
    invited_at timestamptz NOT NULL DEFAULT now(),
    accepted_at timestamptz,
    revoked_at timestamptz,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    CHECK (owner_user_id <> member_user_id),
    UNIQUE (savings_goal_id, member_user_id)
);

CREATE INDEX savings_pocket_members_owner_idx
    ON savings_pocket_members (owner_user_id, status, created_at DESC);

CREATE INDEX savings_pocket_members_member_idx
    ON savings_pocket_members (member_user_id, status, created_at DESC);

CREATE TRIGGER savings_pocket_members_set_updated_at
BEFORE UPDATE ON savings_pocket_members
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

INSERT INTO savings_pocket_categories (name, icon, color) VALUES
    ('Emergency', 'shield', '#DC2626'),
    ('Travel', 'plane', '#0891B2'),
    ('Home', 'home', '#16A34A'),
    ('Bills', 'receipt', '#7C3AED'),
    ('Gifts', 'gift', '#DB2777'),
    ('Investing', 'trending-up', '#EA580C')
ON CONFLICT DO NOTHING;

INSERT INTO data_archival_policies (
    table_name, data_category, retention_days, archive_after_days, archive_strategy, archive_destination,
    legal_hold_supported, delete_after_archive, evidence_required, status, owner
) VALUES
    ('savings_pocket_categories', 'operational', 2555, 365, 'cold_storage_export', 'savings-pocket-archive', true, false, true, 'active', 'product'),
    ('savings_pocket_roundup_rules', 'operational', 2555, 365, 'cold_storage_export', 'savings-pocket-archive', true, false, true, 'active', 'product'),
    ('savings_pocket_recurring_rules', 'operational', 2555, 365, 'cold_storage_export', 'savings-pocket-archive', true, false, true, 'active', 'product'),
    ('savings_pocket_members', 'operational', 2555, 365, 'cold_storage_export', 'savings-pocket-archive', true, false, true, 'active', 'product')
ON CONFLICT (table_name) DO NOTHING;

INSERT INTO privacy_data_inventory (
    table_name, field_name, data_category, classification, lawful_basis, retention_policy, residency_scope, encrypted_at_rest, owner, notes
) VALUES
    ('savings_pocket_categories', '*', 'operational', 'internal', 'contract', 'operational_records_default', 'default', false, 'product', 'Savings pocket category labels, icons and colors'),
    ('savings_pocket_roundup_rules', '*', 'financial', 'confidential', 'contract', 'financial_records_default', 'customer_region', false, 'product', 'Customer-configured automatic round-up rules for savings pockets'),
    ('savings_pocket_recurring_rules', '*', 'financial', 'confidential', 'contract', 'financial_records_default', 'customer_region', false, 'product', 'Customer-configured recurring savings rules'),
    ('savings_pocket_members', '*', 'pii', 'confidential', 'contract', 'operational_records_default', 'customer_region', false, 'product', 'Shared savings pocket membership and roles')
ON CONFLICT (system_name, table_name, field_name) DO NOTHING;