94 lines
3.5 KiB
PL/PgSQL
94 lines
3.5 KiB
PL/PgSQL
-- Migration 032: Create/update stores, employees, redemptions tables
|
|
-- Date: 2026-02-22
|
|
|
|
-- ============================================================
|
|
-- 1. ALTER stores table: add level and parent_id columns
|
|
-- ============================================================
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM information_schema.columns
|
|
WHERE table_name = 'stores' AND column_name = 'level'
|
|
) THEN
|
|
ALTER TABLE stores ADD COLUMN level VARCHAR(50) NOT NULL DEFAULT 'store';
|
|
END IF;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM information_schema.columns
|
|
WHERE table_name = 'stores' AND column_name = 'parent_id'
|
|
) THEN
|
|
ALTER TABLE stores ADD COLUMN parent_id UUID NULL;
|
|
END IF;
|
|
END $$;
|
|
|
|
-- Index for parent_id on stores
|
|
CREATE INDEX IF NOT EXISTS idx_stores_parent ON stores (parent_id);
|
|
|
|
-- Self-referencing FK (store hierarchy)
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM information_schema.table_constraints
|
|
WHERE constraint_name = 'fk_stores_parent' AND table_name = 'stores'
|
|
) THEN
|
|
ALTER TABLE stores ADD CONSTRAINT fk_stores_parent
|
|
FOREIGN KEY (parent_id) REFERENCES stores(id) ON DELETE SET NULL;
|
|
END IF;
|
|
END $$;
|
|
|
|
-- ============================================================
|
|
-- 2. CREATE employees table
|
|
-- ============================================================
|
|
CREATE TABLE IF NOT EXISTS employees (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
issuer_id UUID NOT NULL REFERENCES issuers(id) ON DELETE CASCADE,
|
|
store_id UUID NULL REFERENCES stores(id) ON DELETE SET NULL,
|
|
name VARCHAR(100) NOT NULL,
|
|
phone VARCHAR(20),
|
|
role VARCHAR(50) NOT NULL DEFAULT 'staff',
|
|
status VARCHAR(20) NOT NULL DEFAULT 'active',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_employees_issuer ON employees (issuer_id);
|
|
CREATE INDEX IF NOT EXISTS idx_employees_store ON employees (store_id);
|
|
|
|
-- ============================================================
|
|
-- 3. CREATE redemptions table
|
|
-- ============================================================
|
|
CREATE TABLE IF NOT EXISTS redemptions (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
coupon_id UUID NOT NULL REFERENCES coupons(id) ON DELETE CASCADE,
|
|
issuer_id UUID NOT NULL REFERENCES issuers(id) ON DELETE CASCADE,
|
|
user_id UUID NOT NULL,
|
|
store_id UUID NULL REFERENCES stores(id) ON DELETE SET NULL,
|
|
method VARCHAR(20) NOT NULL,
|
|
amount NUMERIC(12,2) NOT NULL,
|
|
status VARCHAR(20) NOT NULL DEFAULT 'completed',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_redemptions_coupon ON redemptions (coupon_id);
|
|
CREATE INDEX IF NOT EXISTS idx_redemptions_issuer ON redemptions (issuer_id);
|
|
CREATE INDEX IF NOT EXISTS idx_redemptions_user ON redemptions (user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_redemptions_store ON redemptions (store_id);
|
|
CREATE INDEX IF NOT EXISTS idx_redemptions_created ON redemptions (created_at);
|
|
|
|
-- ============================================================
|
|
-- 4. Update trigger for employees.updated_at
|
|
-- ============================================================
|
|
CREATE OR REPLACE FUNCTION update_updated_at_column()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
NEW.updated_at = NOW();
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
DROP TRIGGER IF EXISTS trg_employees_updated_at ON employees;
|
|
CREATE TRIGGER trg_employees_updated_at
|
|
BEFORE UPDATE ON employees
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION update_updated_at_column();
|