-- =============================================================
--  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;

-- -------------------------------------------------------------
-- db_sessions  (PHP database-stored sessions)
-- -------------------------------------------------------------
CREATE TABLE IF NOT EXISTS db_sessions (
    session_id   VARCHAR(128)  PRIMARY KEY,
    data         TEXT          NOT NULL DEFAULT '',
    last_activity BIGINT       NOT NULL DEFAULT 0
);
CREATE INDEX IF NOT EXISTS idx_db_sessions_last_activity ON db_sessions (last_activity);

-- -------------------------------------------------------------
-- system_admins  (Platform superusers)
-- -------------------------------------------------------------
CREATE TABLE IF NOT EXISTS system_admins (
    id            SERIAL        PRIMARY KEY,
    username      VARCHAR(64)   NOT NULL UNIQUE,
    email         VARCHAR(255)  NOT NULL UNIQUE,
    password_hash VARCHAR(255)  NOT NULL,
    full_name     VARCHAR(255)  NOT NULL,
    is_active     BOOLEAN       NOT NULL DEFAULT TRUE,
    last_login_at TIMESTAMPTZ,
    created_at    TIMESTAMPTZ   NOT NULL DEFAULT NOW(),
    updated_at    TIMESTAMPTZ   NOT NULL DEFAULT NOW()
);

-- -------------------------------------------------------------
-- 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()
);

-- -------------------------------------------------------------
-- org_admins  (Organization administrators)
-- -------------------------------------------------------------
CREATE TABLE IF NOT EXISTS org_admins (
    id              SERIAL        PRIMARY KEY,
    organization_id INTEGER       NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    full_name       VARCHAR(255)  NOT NULL,
    email           VARCHAR(255)  NOT NULL,
    phone           VARCHAR(32),
    password_hash   VARCHAR(255)  NOT NULL,
    is_primary      BOOLEAN       NOT NULL DEFAULT FALSE,
    is_active       BOOLEAN       NOT NULL DEFAULT TRUE,
    notify_unassigned BOOLEAN     NOT NULL DEFAULT TRUE,  -- daily 22:00 SMS opt-in
    temp_password   BOOLEAN       NOT NULL DEFAULT TRUE,  -- must change on first login
    last_login_at   TIMESTAMPTZ,
    created_by_system_admin INTEGER REFERENCES system_admins(id) ON DELETE SET NULL,
    created_by_org_admin    INTEGER REFERENCES org_admins(id)    ON DELETE SET NULL,
    created_at      TIMESTAMPTZ   NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ   NOT NULL DEFAULT NOW(),
    UNIQUE (organization_id, email)
);

-- -------------------------------------------------------------
-- members  (Donors)
-- -------------------------------------------------------------
CREATE TABLE IF NOT EXISTS members (
    id              SERIAL        PRIMARY KEY,
    organization_id INTEGER       NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    full_name       VARCHAR(255)  NOT NULL,
    phone           VARCHAR(32)   NOT NULL,
    email           VARCHAR(255),
    magic_token     CHAR(16)      NOT NULL UNIQUE,        -- uppercase alphanumeric, never expires
    password_hash   VARCHAR(255),                         -- optional portal password
    balance         NUMERIC(12,2) NOT NULL DEFAULT 0.00,
    is_active       BOOLEAN       NOT NULL DEFAULT TRUE,
    balance_sms_sent_at TIMESTAMPTZ,                      -- track last balance SMS
    created_by      INTEGER       REFERENCES org_admins(id) ON DELETE SET NULL,
    created_at      TIMESTAMPTZ   NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ   NOT NULL DEFAULT NOW(),
    UNIQUE (organization_id, phone)
);

-- -------------------------------------------------------------
-- 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);

-- -------------------------------------------------------------
-- magic_link_sessions  (Browser sessions for member portal)
-- -------------------------------------------------------------
CREATE TABLE IF NOT EXISTS magic_link_sessions (
    id              SERIAL        PRIMARY KEY,
    member_id       INTEGER       NOT NULL REFERENCES members(id) ON DELETE CASCADE,
    session_token   VARCHAR(64)   NOT NULL UNIQUE,
    ip_address      VARCHAR(45),
    user_agent      TEXT,
    expires_at      TIMESTAMPTZ   NOT NULL,
    created_at      TIMESTAMPTZ   NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_magic_sessions_token  ON magic_link_sessions (session_token);
CREATE INDEX IF NOT EXISTS idx_magic_sessions_member ON magic_link_sessions (member_id);

-- =============================================================
--  SEED DATA
-- =============================================================

-- System admin superuser
-- Password: Admin@123456  (change immediately after first login)
-- Hash generated with: password_hash('Admin@123456', PASSWORD_BCRYPT, ['cost'=>12])
INSERT INTO system_admins (username, email, password_hash, full_name)
VALUES (
    'superadmin',
    'admin@igabay.co.il',
    '$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi',  -- placeholder, regenerate!
    'System Administrator'
)
ON CONFLICT (username) DO NOTHING;

-- =============================================================
--  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;
$$;

DO $$
DECLARE
    t TEXT;
BEGIN
    FOREACH t IN ARRAY ARRAY[
        'system_admins','organizations','org_admins','members',
        'donation_types','donations','payment_types','payments','messages'
    ] LOOP
        EXECUTE format(
            'DROP TRIGGER IF EXISTS set_updated_at ON %I;
             CREATE TRIGGER set_updated_at
             BEFORE UPDATE ON %I
             FOR EACH ROW EXECUTE FUNCTION trigger_set_updated_at();',
            t, t
        );
    END LOOP;
END;
$$;
