-- POS core: enums, tables, and new columns on existing tables

-- New enums
CREATE TYPE "public"."transaction_type" AS ENUM('sale', 'repair_payment', 'rental_deposit', 'account_payment', 'refund');
CREATE TYPE "public"."transaction_status" AS ENUM('pending', 'completed', 'voided', 'refunded');
CREATE TYPE "public"."payment_method" AS ENUM('cash', 'card_present', 'card_keyed', 'check', 'account_charge');
CREATE TYPE "public"."discount_type" AS ENUM('percent', 'fixed');
CREATE TYPE "public"."discount_applies_to" AS ENUM('order', 'line_item', 'category');
CREATE TYPE "public"."drawer_status" AS ENUM('open', 'closed');
CREATE TYPE "public"."tax_category" AS ENUM('goods', 'service', 'exempt');

-- New columns on existing tables
ALTER TABLE "product" ADD COLUMN "tax_category" "tax_category" NOT NULL DEFAULT 'goods';
ALTER TABLE "location" ADD COLUMN "tax_rate" numeric(5, 4) NOT NULL DEFAULT '0';
ALTER TABLE "location" ADD COLUMN "service_tax_rate" numeric(5, 4) NOT NULL DEFAULT '0';

-- Discount table
CREATE TABLE "discount" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
  "location_id" uuid REFERENCES "location"("id"),
  "name" varchar(255) NOT NULL,
  "discount_type" "discount_type" NOT NULL,
  "discount_value" numeric(10, 2) NOT NULL,
  "applies_to" "discount_applies_to" NOT NULL DEFAULT 'line_item',
  "requires_approval_above" numeric(10, 2),
  "is_active" boolean NOT NULL DEFAULT true,
  "valid_from" timestamp with time zone,
  "valid_until" timestamp with time zone,
  "created_at" timestamp with time zone NOT NULL DEFAULT now(),
  "updated_at" timestamp with time zone NOT NULL DEFAULT now()
);

-- Drawer session table
CREATE TABLE "drawer_session" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
  "location_id" uuid REFERENCES "location"("id"),
  "opened_by" uuid NOT NULL REFERENCES "user"("id"),
  "closed_by" uuid REFERENCES "user"("id"),
  "opening_balance" numeric(10, 2) NOT NULL,
  "closing_balance" numeric(10, 2),
  "expected_balance" numeric(10, 2),
  "over_short" numeric(10, 2),
  "denominations" jsonb,
  "status" "drawer_status" NOT NULL DEFAULT 'open',
  "notes" text,
  "opened_at" timestamp with time zone NOT NULL DEFAULT now(),
  "closed_at" timestamp with time zone
);

-- Transaction table
CREATE TABLE "transaction" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
  "location_id" uuid REFERENCES "location"("id"),
  "transaction_number" varchar(50) NOT NULL UNIQUE,
  "account_id" uuid REFERENCES "account"("id"),
  "repair_ticket_id" uuid REFERENCES "repair_ticket"("id"),
  "repair_batch_id" uuid REFERENCES "repair_batch"("id"),
  "transaction_type" "transaction_type" NOT NULL,
  "status" "transaction_status" NOT NULL DEFAULT 'pending',
  "subtotal" numeric(10, 2) NOT NULL DEFAULT '0',
  "discount_total" numeric(10, 2) NOT NULL DEFAULT '0',
  "tax_total" numeric(10, 2) NOT NULL DEFAULT '0',
  "total" numeric(10, 2) NOT NULL DEFAULT '0',
  "payment_method" "payment_method",
  "amount_tendered" numeric(10, 2),
  "change_given" numeric(10, 2),
  "check_number" varchar(50),
  "stripe_payment_intent_id" varchar(255),
  "tax_exempt" boolean NOT NULL DEFAULT false,
  "tax_exempt_reason" text,
  "processed_by" uuid NOT NULL REFERENCES "user"("id"),
  "drawer_session_id" uuid REFERENCES "drawer_session"("id"),
  "notes" text,
  "completed_at" timestamp with time zone,
  "created_at" timestamp with time zone NOT NULL DEFAULT now(),
  "updated_at" timestamp with time zone NOT NULL DEFAULT now()
);

-- Transaction line item table
CREATE TABLE "transaction_line_item" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
  "transaction_id" uuid NOT NULL REFERENCES "transaction"("id"),
  "product_id" uuid REFERENCES "product"("id"),
  "inventory_unit_id" uuid REFERENCES "inventory_unit"("id"),
  "description" varchar(255) NOT NULL,
  "qty" integer NOT NULL DEFAULT 1,
  "unit_price" numeric(10, 2) NOT NULL,
  "discount_amount" numeric(10, 2) NOT NULL DEFAULT '0',
  "discount_reason" text,
  "tax_rate" numeric(5, 4) NOT NULL DEFAULT '0',
  "tax_amount" numeric(10, 2) NOT NULL DEFAULT '0',
  "line_total" numeric(10, 2) NOT NULL DEFAULT '0',
  "created_at" timestamp with time zone NOT NULL DEFAULT now()
);

-- Discount audit table (append-only)
CREATE TABLE "discount_audit" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
  "transaction_id" uuid NOT NULL REFERENCES "transaction"("id"),
  "transaction_line_item_id" uuid REFERENCES "transaction_line_item"("id"),
  "discount_id" uuid REFERENCES "discount"("id"),
  "applied_by" uuid NOT NULL REFERENCES "user"("id"),
  "approved_by" uuid REFERENCES "user"("id"),
  "original_amount" numeric(10, 2) NOT NULL,
  "discounted_amount" numeric(10, 2) NOT NULL,
  "reason" text NOT NULL,
  "created_at" timestamp with time zone NOT NULL DEFAULT now()
);

-- SKU unique partial index (from prior untracked migration)
CREATE UNIQUE INDEX IF NOT EXISTS products_sku_unique ON product (sku) WHERE sku IS NOT NULL;
