-- 1. PACKAGES TABLE
CREATE TABLE packages (
  id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name         TEXT NOT NULL,
  price        NUMERIC NOT NULL,
  visits       INT NOT NULL,
  period       TEXT,
  description  TEXT,
  is_active    BOOLEAN DEFAULT true,
  created_at   TIMESTAMPTZ DEFAULT now()
);

-- 2. PATRONS TABLE
CREATE TABLE patrons (
  id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name         TEXT NOT NULL,
  phone        TEXT NOT NULL,
  card_id      TEXT UNIQUE NOT NULL,
  package_id   UUID REFERENCES packages(id),
  package_name TEXT,
  visits_used  INT DEFAULT 0,
  max_visits   INT NOT NULL,
  price_paid   NUMERIC NOT NULL,
  status       TEXT DEFAULT 'active',
  visit_dates  TEXT[] DEFAULT '{}',
  created_at   TIMESTAMPTZ DEFAULT now()
);

-- 3. TRANSACTIONS TABLE
CREATE TABLE transactions (
  id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  patron_id    UUID REFERENCES patrons(id),
  patron_name  TEXT,
  phone        TEXT,
  type         TEXT,
  package_name TEXT,
  card_id      TEXT,
  amount       NUMERIC DEFAULT 0,
  created_at   TIMESTAMPTZ DEFAULT now()
);

-- 4. ENABLE RLS (Security)
ALTER TABLE packages     ENABLE ROW LEVEL SECURITY;
ALTER TABLE patrons      ENABLE ROW LEVEL SECURITY;
ALTER TABLE transactions ENABLE ROW LEVEL SECURITY;

-- 5. KEEP-ALIVE PING TABLE (for cron job - anon safe)
CREATE TABLE keep_alive (id INT DEFAULT 1, pinged_at TIMESTAMPTZ DEFAULT now());
INSERT INTO keep_alive VALUES (1, now());
CREATE POLICY "allow_ping" ON keep_alive FOR SELECT TO anon USING (true);

-- 6. RLS POLICIES (authenticated staff/owner access)
CREATE POLICY "auth_all" ON packages     FOR ALL TO authenticated USING (true);
CREATE POLICY "auth_all" ON patrons      FOR ALL TO authenticated USING (true);
CREATE POLICY "auth_all" ON transactions FOR ALL TO authenticated USING (true);

-- 7. INSERT DEFAULT PACKAGE
INSERT INTO packages (name, price, visits, period, description)
VALUES ('Standard', 20, 3, 'Ongoing', '3 haircut visits for $20');