BEGIN;

CREATE SCHEMA IF NOT EXISTS settings;

-- Generic tenant/user section store for simple SettingsPage panels that are
-- edited as one configuration payload rather than as child records.
CREATE TABLE IF NOT EXISTS settings.setting_sections (
    id bigserial PRIMARY KEY,
    tenant_id uuid NULL,
    user_id uuid NULL,
    scope character varying(30) NOT NULL DEFAULT 'tenant',
    section_group character varying(80) NOT NULL,
    section_key character varying(120) NOT NULL,
    section_label character varying(160) NOT NULL,
    settings_payload jsonb NOT NULL DEFAULT '{}'::jsonb,
    status character varying(40) NOT NULL DEFAULT 'active',
    is_active boolean NOT NULL DEFAULT TRUE,
    created_by uuid NULL,
    updated_by uuid NULL,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted_at timestamp without time zone NULL,
    deleted_by uuid NULL,
    CONSTRAINT chk_setting_sections_scope
        CHECK (scope IN ('tenant', 'user', 'platform')),
    CONSTRAINT fk_setting_sections_tenant
        FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_setting_sections_user
        FOREIGN KEY (user_id) REFERENCES iam.users(id) ON DELETE CASCADE
);

CREATE UNIQUE INDEX IF NOT EXISTS uq_setting_sections_tenant_key
    ON settings.setting_sections (tenant_id, section_key)
    WHERE scope = 'tenant'
      AND tenant_id IS NOT NULL
      AND user_id IS NULL
      AND deleted_at IS NULL;

CREATE UNIQUE INDEX IF NOT EXISTS uq_setting_sections_user_key
    ON settings.setting_sections (tenant_id, user_id, section_key)
    WHERE scope = 'user'
      AND tenant_id IS NOT NULL
      AND user_id IS NOT NULL
      AND deleted_at IS NULL;

CREATE UNIQUE INDEX IF NOT EXISTS uq_setting_sections_platform_key
    ON settings.setting_sections (section_key)
    WHERE scope = 'platform'
      AND tenant_id IS NULL
      AND user_id IS NULL
      AND deleted_at IS NULL;

CREATE INDEX IF NOT EXISTS idx_setting_sections_tenant
    ON settings.setting_sections (tenant_id, section_group, section_key)
    WHERE deleted_at IS NULL;

CREATE INDEX IF NOT EXISTS idx_setting_sections_user
    ON settings.setting_sections (user_id, section_group, section_key)
    WHERE deleted_at IS NULL;

CREATE TABLE IF NOT EXISTS settings.financial_periods (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    fiscal_year integer NOT NULL,
    period_code character varying(50) NOT NULL,
    period_name character varying(150) NOT NULL,
    period_type character varying(40) NOT NULL DEFAULT 'quarter',
    start_date date NOT NULL,
    end_date date NOT NULL,
    status character varying(40) NOT NULL DEFAULT 'future',
    closed_at timestamp without time zone NULL,
    closed_by uuid NULL,
    is_active boolean NOT NULL DEFAULT TRUE,
    created_by uuid NULL,
    updated_by uuid NULL,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted_at timestamp without time zone NULL,
    deleted_by uuid NULL,
    CONSTRAINT fk_financial_periods_tenant
        FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE,
    CONSTRAINT uq_financial_periods_code
        UNIQUE (tenant_id, period_code),
    CONSTRAINT chk_financial_period_dates
        CHECK (end_date >= start_date)
);

CREATE INDEX IF NOT EXISTS idx_financial_periods_tenant
    ON settings.financial_periods (tenant_id, fiscal_year, start_date)
    WHERE deleted_at IS NULL;

CREATE TABLE IF NOT EXISTS settings.payment_terms (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    term_code character varying(50) NOT NULL,
    term_name character varying(120) NOT NULL,
    term_type character varying(40) NOT NULL DEFAULT 'both',
    due_days integer NOT NULL DEFAULT 0,
    grace_days integer NOT NULL DEFAULT 0,
    early_discount_percent numeric(8, 4) NULL,
    early_discount_days integer NULL,
    is_default_customer boolean NOT NULL DEFAULT FALSE,
    is_default_supplier boolean NOT NULL DEFAULT FALSE,
    is_active boolean NOT NULL DEFAULT TRUE,
    created_by uuid NULL,
    updated_by uuid NULL,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted_at timestamp without time zone NULL,
    deleted_by uuid NULL,
    CONSTRAINT fk_payment_terms_tenant
        FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE,
    CONSTRAINT uq_payment_terms_code
        UNIQUE (tenant_id, term_code)
);

CREATE INDEX IF NOT EXISTS idx_payment_terms_tenant
    ON settings.payment_terms (tenant_id, term_type)
    WHERE deleted_at IS NULL;

CREATE TABLE IF NOT EXISTS settings.notification_channels (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    channel_code character varying(50) NOT NULL,
    channel_name character varying(120) NOT NULL,
    channel_type character varying(40) NOT NULL,
    provider character varying(120) NULL,
    from_address character varying(180) NULL,
    from_number character varying(80) NULL,
    config jsonb NOT NULL DEFAULT '{}'::jsonb,
    secret_reference character varying(255) NULL,
    is_enabled boolean NOT NULL DEFAULT TRUE,
    is_configured boolean NOT NULL DEFAULT FALSE,
    is_active boolean NOT NULL DEFAULT TRUE,
    created_by uuid NULL,
    updated_by uuid NULL,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted_at timestamp without time zone NULL,
    deleted_by uuid NULL,
    CONSTRAINT fk_notification_channels_tenant
        FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE,
    CONSTRAINT uq_notification_channels_code
        UNIQUE (tenant_id, channel_code)
);

CREATE INDEX IF NOT EXISTS idx_notification_channels_tenant
    ON settings.notification_channels (tenant_id, channel_type)
    WHERE deleted_at IS NULL;

CREATE TABLE IF NOT EXISTS settings.notification_event_rules (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    event_code character varying(100) NOT NULL,
    event_name character varying(160) NOT NULL,
    category character varying(80) NOT NULL,
    description text NULL,
    priority character varying(30) NOT NULL DEFAULT 'medium',
    channels jsonb NOT NULL DEFAULT '{"email": true, "sms": false, "in_app": true}'::jsonb,
    is_enabled boolean NOT NULL DEFAULT TRUE,
    created_by uuid NULL,
    updated_by uuid NULL,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted_at timestamp without time zone NULL,
    deleted_by uuid NULL,
    CONSTRAINT fk_notification_event_rules_tenant
        FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE,
    CONSTRAINT uq_notification_event_rules_code
        UNIQUE (tenant_id, event_code)
);

CREATE INDEX IF NOT EXISTS idx_notification_event_rules_tenant
    ON settings.notification_event_rules (tenant_id, category, priority)
    WHERE deleted_at IS NULL;

CREATE TABLE IF NOT EXISTS settings.notification_templates (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    event_rule_id bigint NULL,
    template_code character varying(100) NOT NULL,
    template_name character varying(160) NOT NULL,
    channel_type character varying(40) NOT NULL,
    subject text NULL,
    body text NOT NULL,
    variables jsonb NOT NULL DEFAULT '[]'::jsonb,
    locale character varying(20) NOT NULL DEFAULT 'en',
    status character varying(40) NOT NULL DEFAULT 'active',
    is_active boolean NOT NULL DEFAULT TRUE,
    created_by uuid NULL,
    updated_by uuid NULL,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted_at timestamp without time zone NULL,
    deleted_by uuid NULL,
    CONSTRAINT fk_notification_templates_tenant
        FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_notification_templates_event_rule
        FOREIGN KEY (event_rule_id) REFERENCES settings.notification_event_rules(id) ON DELETE SET NULL,
    CONSTRAINT uq_notification_templates_code_channel_locale
        UNIQUE (tenant_id, template_code, channel_type, locale)
);

CREATE INDEX IF NOT EXISTS idx_notification_templates_tenant
    ON settings.notification_templates (tenant_id, channel_type, template_code)
    WHERE deleted_at IS NULL;

CREATE TABLE IF NOT EXISTS settings.integration_connections (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    integration_code character varying(80) NOT NULL,
    integration_name character varying(160) NOT NULL,
    category character varying(60) NOT NULL,
    description text NULL,
    status character varying(40) NOT NULL DEFAULT 'disconnected',
    environment character varying(40) NOT NULL DEFAULT 'sandbox',
    is_enabled boolean NOT NULL DEFAULT FALSE,
    config jsonb NOT NULL DEFAULT '{}'::jsonb,
    credential_reference character varying(255) NULL,
    webhook_url text NULL,
    last_sync_at timestamp without time zone NULL,
    last_error text NULL,
    created_by uuid NULL,
    updated_by uuid NULL,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted_at timestamp without time zone NULL,
    deleted_by uuid NULL,
    CONSTRAINT fk_integration_connections_tenant
        FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE,
    CONSTRAINT uq_integration_connections_code
        UNIQUE (tenant_id, integration_code)
);

CREATE INDEX IF NOT EXISTS idx_integration_connections_tenant
    ON settings.integration_connections (tenant_id, category, status)
    WHERE deleted_at IS NULL;

CREATE TABLE IF NOT EXISTS settings.webhook_endpoints (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    integration_connection_id bigint NULL,
    webhook_code character varying(80) NOT NULL,
    webhook_name character varying(160) NOT NULL,
    endpoint_url text NOT NULL,
    event_filters jsonb NOT NULL DEFAULT '[]'::jsonb,
    signing_secret_reference character varying(255) NULL,
    retry_policy jsonb NOT NULL DEFAULT '{}'::jsonb,
    status character varying(40) NOT NULL DEFAULT 'active',
    last_delivery_at timestamp without time zone NULL,
    last_delivery_status character varying(40) NULL,
    created_by uuid NULL,
    updated_by uuid NULL,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted_at timestamp without time zone NULL,
    deleted_by uuid NULL,
    CONSTRAINT fk_webhook_endpoints_tenant
        FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_webhook_endpoints_integration
        FOREIGN KEY (integration_connection_id) REFERENCES settings.integration_connections(id) ON DELETE SET NULL,
    CONSTRAINT uq_webhook_endpoints_code
        UNIQUE (tenant_id, webhook_code)
);

CREATE INDEX IF NOT EXISTS idx_webhook_endpoints_tenant
    ON settings.webhook_endpoints (tenant_id, status)
    WHERE deleted_at IS NULL;

CREATE TABLE IF NOT EXISTS settings.api_keys (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    key_name character varying(160) NOT NULL,
    key_prefix character varying(80) NOT NULL,
    key_hash text NOT NULL,
    scope character varying(40) NOT NULL DEFAULT 'read',
    permissions jsonb NOT NULL DEFAULT '{"read": true, "write": false, "webhooks": false}'::jsonb,
    ip_allowlist jsonb NOT NULL DEFAULT '[]'::jsonb,
    status character varying(40) NOT NULL DEFAULT 'active',
    expires_at timestamp without time zone NULL,
    last_used_at timestamp without time zone NULL,
    revoked_at timestamp without time zone NULL,
    revoked_by uuid NULL,
    created_by uuid NULL,
    updated_by uuid NULL,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted_at timestamp without time zone NULL,
    deleted_by uuid NULL,
    CONSTRAINT fk_api_keys_tenant
        FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE,
    CONSTRAINT uq_api_keys_prefix
        UNIQUE (key_prefix)
);

CREATE INDEX IF NOT EXISTS idx_api_keys_tenant
    ON settings.api_keys (tenant_id, status)
    WHERE deleted_at IS NULL;

CREATE TABLE IF NOT EXISTS settings.subscription_plans (
    id bigserial PRIMARY KEY,
    plan_code character varying(50) NOT NULL UNIQUE,
    plan_name character varying(120) NOT NULL,
    tagline text NULL,
    monthly_amount numeric(18, 2) NOT NULL DEFAULT 0,
    annual_monthly_amount numeric(18, 2) NOT NULL DEFAULT 0,
    currency_code character varying(10) NOT NULL DEFAULT 'GHS',
    features jsonb NOT NULL DEFAULT '[]'::jsonb,
    limits jsonb NOT NULL DEFAULT '{}'::jsonb,
    badge character varying(80) NULL,
    sort_order integer NOT NULL DEFAULT 1,
    is_active boolean NOT NULL DEFAULT TRUE,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS settings.tenant_subscriptions (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    subscription_plan_id bigint NULL,
    plan_code character varying(50) NOT NULL,
    billing_cycle character varying(40) NOT NULL DEFAULT 'monthly',
    status character varying(40) NOT NULL DEFAULT 'active',
    seats_used integer NOT NULL DEFAULT 0,
    seats_total integer NULL,
    storage_used_mb integer NOT NULL DEFAULT 0,
    storage_limit_mb integer NULL,
    auto_renew boolean NOT NULL DEFAULT TRUE,
    current_period_start date NULL,
    current_period_end date NULL,
    trial_ends_at timestamp without time zone NULL,
    paused_at timestamp without time zone NULL,
    cancelled_at timestamp without time zone NULL,
    cancel_reason text NULL,
    created_by uuid NULL,
    updated_by uuid NULL,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted_at timestamp without time zone NULL,
    deleted_by uuid NULL,
    CONSTRAINT fk_tenant_subscriptions_tenant
        FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_tenant_subscriptions_plan
        FOREIGN KEY (subscription_plan_id) REFERENCES settings.subscription_plans(id) ON DELETE SET NULL,
    CONSTRAINT uq_tenant_subscriptions_tenant
        UNIQUE (tenant_id)
);

CREATE INDEX IF NOT EXISTS idx_tenant_subscriptions_status
    ON settings.tenant_subscriptions (status, current_period_end)
    WHERE deleted_at IS NULL;

CREATE TABLE IF NOT EXISTS settings.user_preferences (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    user_id uuid NOT NULL,
    display_name character varying(180) NULL,
    profile_image_url text NULL,
    default_landing_page character varying(120) NULL,
    compact_mode boolean NOT NULL DEFAULT FALSE,
    show_keyboard_shortcuts boolean NOT NULL DEFAULT TRUE,
    auto_expand_sidebar boolean NOT NULL DEFAULT TRUE,
    theme character varying(40) NOT NULL DEFAULT 'light',
    font_size character varying(40) NOT NULL DEFAULT 'medium',
    sidebar_density character varying(40) NOT NULL DEFAULT 'default',
    animate_transitions boolean NOT NULL DEFAULT TRUE,
    interface_language character varying(20) NOT NULL DEFAULT 'en-US',
    number_format character varying(40) NOT NULL DEFAULT '1,234.56',
    date_format character varying(40) NOT NULL DEFAULT 'MM/DD/YYYY',
    timezone character varying(120) NULL,
    use_24h_time boolean NOT NULL DEFAULT FALSE,
    show_relative_times boolean NOT NULL DEFAULT TRUE,
    preferences_payload jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_user_preferences_tenant
        FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_user_preferences_user
        FOREIGN KEY (user_id) REFERENCES iam.users(id) ON DELETE CASCADE,
    CONSTRAINT uq_user_preferences_user
        UNIQUE (tenant_id, user_id)
);

CREATE TABLE IF NOT EXISTS settings.platform_system_settings (
    id bigserial PRIMARY KEY,
    setting_key character varying(120) NOT NULL UNIQUE,
    setting_group character varying(80) NOT NULL,
    setting_label character varying(160) NOT NULL,
    setting_payload jsonb NOT NULL DEFAULT '{}'::jsonb,
    is_sensitive boolean NOT NULL DEFAULT FALSE,
    is_active boolean NOT NULL DEFAULT TRUE,
    updated_by uuid NULL,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
);

COMMIT;