-- SIMO 1 Database Schema
-- Run: psql -U postgres -d simo1_db -f server/schema.sql

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE IF NOT EXISTS simo_customers (
  id          UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name        VARCHAR(255)  NOT NULL,
  phone       VARCHAR(50)   NOT NULL,
  email       VARCHAR(255),
  address     TEXT,
  line_id     VARCHAR(100),
  created_at  TIMESTAMPTZ   NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS simo_machines (
  id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  customer_id     UUID NOT NULL REFERENCES simo_customers(id) ON DELETE CASCADE,
  serial_number   VARCHAR(100) NOT NULL UNIQUE,
  purchase_date   DATE NOT NULL,
  warranty_expiry DATE NOT NULL,
  color           VARCHAR(100),
  notes           TEXT
);

CREATE TABLE IF NOT EXISTS simo_transactions (
  id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  customer_id     UUID NOT NULL REFERENCES simo_customers(id) ON DELETE CASCADE,
  machine_id      UUID NOT NULL REFERENCES simo_machines(id) ON DELETE CASCADE,
  amount          NUMERIC(12,2) NOT NULL,
  payment_method  VARCHAR(20)   NOT NULL CHECK (payment_method IN ('cash','promptpay')),
  date            DATE NOT NULL,
  note            TEXT
);

CREATE TABLE IF NOT EXISTS simo_reports (
  id          UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  customer_id UUID NOT NULL REFERENCES simo_customers(id) ON DELETE CASCADE,
  machine_id  UUID NOT NULL REFERENCES simo_machines(id) ON DELETE CASCADE,
  category    VARCHAR(50) NOT NULL,
  detail      TEXT,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  status      VARCHAR(20) NOT NULL DEFAULT 'pending' CHECK (status IN ('pending','resolved')),
  admin_note  TEXT
);

CREATE INDEX IF NOT EXISTS idx_simo_machines_customer     ON simo_machines(customer_id);
CREATE INDEX IF NOT EXISTS idx_simo_transactions_customer ON simo_transactions(customer_id);
CREATE INDEX IF NOT EXISTS idx_simo_transactions_machine  ON simo_transactions(machine_id);
CREATE INDEX IF NOT EXISTS idx_simo_reports_customer      ON simo_reports(customer_id);
CREATE INDEX IF NOT EXISTS idx_simo_reports_status        ON simo_reports(status);
