-- Meshkee CMS — payment transactions

-- ---------------------------------------------------------------------------
-- enums
-- ---------------------------------------------------------------------------
DO $$ BEGIN
  CREATE TYPE transaction_type AS ENUM (
    'pos',
    'cash',
    'transfer',
    'e_payment_gate'
  );
EXCEPTION
  WHEN duplicate_object THEN NULL;
END $$;

DO $$ BEGIN
  CREATE TYPE transaction_status AS ENUM (
    'pending',
    'completed',
    'failed',
    'refunded'
  );
EXCEPTION
  WHEN duplicate_object THEN NULL;
END $$;

-- ---------------------------------------------------------------------------
-- transactions
-- ---------------------------------------------------------------------------
CREATE TABLE transactions (
  id                   BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  business_id          BIGINT NOT NULL,
  order_id             BIGINT,
  user_id              BIGINT NOT NULL,
  type                 transaction_type NOT NULL,
  status               transaction_status NOT NULL DEFAULT 'pending',
  amount               NUMERIC(12, 2) NOT NULL,
  pos_type             VARCHAR(100),
  gateway_type         VARCHAR(100),
  transfer_account     VARCHAR(255),
  transfer_ref_number  VARCHAR(100),
  notes                TEXT,
  created_by           BIGINT,
  created_at           TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at           TIMESTAMPTZ NOT NULL DEFAULT NOW(),

  CONSTRAINT transactions_business_id_fkey
    FOREIGN KEY (business_id) REFERENCES businesses (id) ON DELETE CASCADE,
  CONSTRAINT transactions_order_id_fkey
    FOREIGN KEY (order_id) REFERENCES orders (id) ON DELETE SET NULL,
  CONSTRAINT transactions_user_id_fkey
    FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE RESTRICT,
  CONSTRAINT transactions_created_by_fkey
    FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL,
  CONSTRAINT transactions_amount_non_negative CHECK (amount >= 0),
  CONSTRAINT transactions_type_fields_check CHECK (
    (type = 'pos'
      AND pos_type IS NOT NULL
      AND gateway_type IS NULL
      AND transfer_account IS NULL
      AND transfer_ref_number IS NULL)
    OR (type = 'cash'
      AND pos_type IS NULL
      AND gateway_type IS NULL
      AND transfer_account IS NULL
      AND transfer_ref_number IS NULL)
    OR (type = 'transfer'
      AND transfer_account IS NOT NULL
      AND transfer_ref_number IS NOT NULL
      AND pos_type IS NULL
      AND gateway_type IS NULL)
    OR (type = 'e_payment_gate'
      AND gateway_type IS NOT NULL
      AND pos_type IS NULL
      AND transfer_account IS NULL
      AND transfer_ref_number IS NULL)
  )
);

CREATE INDEX idx_transactions_business_created
  ON transactions (business_id, created_at DESC);

CREATE INDEX idx_transactions_order_id
  ON transactions (order_id);

CREATE INDEX idx_transactions_business_user
  ON transactions (business_id, user_id, created_at DESC);

CREATE TRIGGER transactions_set_updated_at
  BEFORE UPDATE ON transactions
  FOR EACH ROW
  EXECUTE FUNCTION set_updated_at();

-- ---------------------------------------------------------------------------
-- permissions
-- ---------------------------------------------------------------------------
INSERT INTO permissions (name, slug, group_name, description) VALUES
  ('View transactions', 'transactions.read', 'transactions', 'View payment transactions')
ON CONFLICT (slug) DO NOTHING;

INSERT INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id
FROM roles r
JOIN permissions p ON p.slug = 'transactions.read'
WHERE r.slug IN ('business_owner', 'owner', 'admin')
ON CONFLICT DO NOTHING;

INSERT INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id
FROM roles r
JOIN permissions p ON p.slug = 'transactions.read'
WHERE r.slug = 'editor'
ON CONFLICT DO NOTHING;