-- =============================================================
--  DonationManager — Initial Schema Migration
--  PostgreSQL 15  |  Run once on fresh database
--  Connect: psql -U igabay_user -d igabay_db -p 5433 -f 001_initial.sql
-- =============================================================

-- Extensions
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- -------------------------------------------------------------
-- organizations  (Tenant organizations)
-- -------------------------------------------------------------
CREATE TABLE IF NOT EXISTS organizations (
    id              SERIAL        PRIMARY KEY,
    name            VARCHAR(255)  NOT NULL,
    subdomain       VARCHAR(63)   NOT NULL UNIQUE,
    country         CHAR(2)       NOT NULL DEFAULT 'US',
    currency        CHAR(3)       NOT NULL DEFAULT 'USD',
    language        VARCHAR(10)   NOT NULL DEFAULT 'en',
    contact_name    VARCHAR(255),
    contact_email   VARCHAR(255),
    contact_phone   VARCHAR(32),
    address         TEXT,
    logo            BYTEA,                        -- stored as AVIF
    is_active       BOOLEAN       NOT NULL DEFAULT TRUE,
    created_by      INTEGER       REFERENCES system_admins(id) ON DELETE SET NULL,
    created_at      TIMESTAMPTZ   NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ   NOT NULL DEFAULT NOW()
);

-- -------------------------------------------------------------
-- donation_types  (Reusable auction types per org)
-- -------------------------------------------------------------
CREATE TABLE IF NOT EXISTS donation_types (
    id              SERIAL        PRIMARY KEY,
    organization_id INTEGER       NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    name            VARCHAR(255)  NOT NULL,
    day_of_week     SMALLINT      CHECK (day_of_week BETWEEN 0 AND 6),  -- 0=Sun … 6=Sat
    auto_create     BOOLEAN       NOT NULL DEFAULT FALSE,
    is_active       BOOLEAN       NOT NULL DEFAULT TRUE,
    created_at      TIMESTAMPTZ   NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ   NOT NULL DEFAULT NOW(),
    UNIQUE (organization_id, name)
);

-- -------------------------------------------------------------
-- donations  (Individual auction records)
-- -------------------------------------------------------------
CREATE TABLE IF NOT EXISTS donations (
    id              SERIAL        PRIMARY KEY,
    organization_id INTEGER       NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    donation_type_id INTEGER      NOT NULL REFERENCES donation_types(id) ON DELETE RESTRICT,
    donation_date   DATE          NOT NULL,
    winner_id       INTEGER       REFERENCES members(id) ON DELETE SET NULL,
    amount          NUMERIC(12,2),
    notes           TEXT,
    assigned_by     INTEGER       REFERENCES org_admins(id) ON DELETE SET NULL,
    assigned_at     TIMESTAMPTZ,
    created_at      TIMESTAMPTZ   NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ   NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_donations_org_date   ON donations (organization_id, donation_date);
CREATE INDEX IF NOT EXISTS idx_donations_winner     ON donations (winner_id);
CREATE INDEX IF NOT EXISTS idx_donations_unassigned ON donations (organization_id) WHERE winner_id IS NULL;

-- -------------------------------------------------------------
-- payment_types  (Org-configured payment methods)
-- -------------------------------------------------------------
CREATE TABLE IF NOT EXISTS payment_types (
    id              SERIAL        PRIMARY KEY,
    organization_id INTEGER       NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    name            VARCHAR(128)  NOT NULL,
    is_active       BOOLEAN       NOT NULL DEFAULT TRUE,
    sort_order      SMALLINT      NOT NULL DEFAULT 0,
    created_at      TIMESTAMPTZ   NOT NULL DEFAULT NOW(),
    UNIQUE (organization_id, name)
);

-- -------------------------------------------------------------
-- payments  (Member payment submissions)
-- -------------------------------------------------------------
CREATE TABLE IF NOT EXISTS payments (
    id              SERIAL        PRIMARY KEY,
    organization_id INTEGER       NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    member_id       INTEGER       NOT NULL REFERENCES members(id)        ON DELETE CASCADE,
    payment_type_id INTEGER       NOT NULL REFERENCES payment_types(id)  ON DELETE RESTRICT,
    amount          NUMERIC(12,2) NOT NULL CHECK (amount > 0),
    notes           TEXT,
    status          VARCHAR(16)   NOT NULL DEFAULT 'pending'
                    CHECK (status IN ('pending','approved','rejected')),
    reviewed_by     INTEGER       REFERENCES org_admins(id) ON DELETE SET NULL,
    reviewed_at     TIMESTAMPTZ,
    created_at      TIMESTAMPTZ   NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ   NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_payments_member  ON payments (member_id);
CREATE INDEX IF NOT EXISTS idx_payments_pending ON payments (organization_id) WHERE status = 'pending';

-- -------------------------------------------------------------
-- messages  (Community notice board)
-- -------------------------------------------------------------
CREATE TABLE IF NOT EXISTS messages (
    id              SERIAL        PRIMARY KEY,
    organization_id INTEGER       NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    title           VARCHAR(255)  NOT NULL,
    body            TEXT          NOT NULL,
    is_members_only BOOLEAN       NOT NULL DEFAULT FALSE,
    is_published    BOOLEAN       NOT NULL DEFAULT TRUE,
    posted_by       INTEGER       REFERENCES org_admins(id) ON DELETE SET NULL,
    created_at      TIMESTAMPTZ   NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ   NOT NULL DEFAULT NOW()
);

-- -------------------------------------------------------------
-- notifications_log  (SMS send history)
-- -------------------------------------------------------------
CREATE TABLE IF NOT EXISTS notifications_log (
    id              SERIAL        PRIMARY KEY,
    organization_id INTEGER       REFERENCES organizations(id) ON DELETE SET NULL,
    recipient_type  VARCHAR(16)   NOT NULL CHECK (recipient_type IN ('member','org_admin')),
    recipient_id    INTEGER       NOT NULL,
    phone           VARCHAR(32)   NOT NULL,
    message_type    VARCHAR(64)   NOT NULL,   -- 'balance_update','unassigned_donations','welcome'
    message_body    TEXT          NOT NULL,
    status          VARCHAR(16)   NOT NULL DEFAULT 'sent'
                    CHECK (status IN ('sent','failed','pending')),
    provider_response TEXT,
    sent_at         TIMESTAMPTZ   NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_notifications_recipient ON notifications_log (recipient_type, recipient_id);

-- =============================================================
--  HELPER: updated_at auto-trigger
-- =============================================================
CREATE OR REPLACE FUNCTION trigger_set_updated_at()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$;
