-- Accounting module tables

-- Enums
DO $$ BEGIN
  CREATE TYPE invoice_status AS ENUM ('draft','sent','paid','partial','overdue','void','written_off');
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

DO $$ BEGIN
  CREATE TYPE account_type AS ENUM ('asset','liability','revenue','contra_revenue','cogs','expense');
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

DO $$ BEGIN
  CREATE TYPE normal_balance AS ENUM ('debit','credit');
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

DO $$ BEGIN
  CREATE TYPE journal_line_type AS ENUM ('debit','credit');
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

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

-- Core: Invoice
CREATE TABLE IF NOT EXISTS "invoice" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  "invoice_number" varchar(50) UNIQUE NOT NULL,
  "account_id" uuid NOT NULL REFERENCES "account"("id"),
  "location_id" uuid REFERENCES "location"("id"),
  "status" invoice_status NOT NULL DEFAULT 'draft',
  "issue_date" date NOT NULL DEFAULT CURRENT_DATE,
  "due_date" date NOT NULL DEFAULT CURRENT_DATE,
  "source_type" varchar(50),
  "source_id" uuid,
  "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,
  "amount_paid" numeric(10,2) NOT NULL DEFAULT 0,
  "balance" numeric(10,2) NOT NULL DEFAULT 0,
  "refund_of_invoice_id" uuid REFERENCES "invoice"("id"),
  "notes" text,
  "created_by" uuid REFERENCES "user"("id"),
  "created_at" timestamptz NOT NULL DEFAULT now(),
  "updated_at" timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS "invoice_account_id" ON "invoice" ("account_id");
CREATE INDEX IF NOT EXISTS "invoice_status" ON "invoice" ("status");
CREATE INDEX IF NOT EXISTS "invoice_source" ON "invoice" ("source_type", "source_id");

-- Core: Invoice Line Item
CREATE TABLE IF NOT EXISTS "invoice_line_item" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  "invoice_id" uuid NOT NULL REFERENCES "invoice"("id") ON DELETE CASCADE,
  "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,
  "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,
  "account_code_id" uuid,
  "source_type" varchar(50),
  "source_id" uuid,
  "created_at" timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS "invoice_line_item_invoice_id" ON "invoice_line_item" ("invoice_id");

-- Core: Payment Application
CREATE TABLE IF NOT EXISTS "payment_application" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  "invoice_id" uuid NOT NULL REFERENCES "invoice"("id"),
  "transaction_id" uuid REFERENCES "transaction"("id"),
  "amount" numeric(10,2) NOT NULL,
  "applied_at" timestamptz NOT NULL DEFAULT now(),
  "applied_by" uuid NOT NULL REFERENCES "user"("id"),
  "notes" text,
  "created_at" timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS "payment_application_invoice_id" ON "payment_application" ("invoice_id");

-- Core: Account Balance (materialized AR)
CREATE TABLE IF NOT EXISTS "account_balance" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  "account_id" uuid NOT NULL UNIQUE REFERENCES "account"("id"),
  "current_balance" numeric(10,2) NOT NULL DEFAULT 0,
  "last_invoice_date" date,
  "last_payment_date" date,
  "updated_at" timestamptz NOT NULL DEFAULT now()
);

-- Accounting module: Chart of Accounts
CREATE TABLE IF NOT EXISTS "account_code" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  "code" varchar(10) UNIQUE NOT NULL,
  "name" varchar(255) NOT NULL,
  "account_type" account_type NOT NULL,
  "normal_balance" normal_balance NOT NULL,
  "is_system" boolean NOT NULL DEFAULT true,
  "is_active" boolean NOT NULL DEFAULT true,
  "created_at" timestamptz NOT NULL DEFAULT now(),
  "updated_at" timestamptz NOT NULL DEFAULT now()
);

-- Accounting module: Journal Entry
CREATE TABLE IF NOT EXISTS "journal_entry" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  "entry_number" varchar(20) UNIQUE NOT NULL,
  "entry_date" date NOT NULL DEFAULT CURRENT_DATE,
  "entry_type" varchar(50) NOT NULL,
  "source_type" varchar(50),
  "source_id" uuid,
  "description" text NOT NULL,
  "total_debits" numeric(10,2) NOT NULL,
  "total_credits" numeric(10,2) NOT NULL,
  "is_void" boolean NOT NULL DEFAULT false,
  "void_reason" text,
  "voided_by" uuid REFERENCES "user"("id"),
  "voided_at" timestamptz,
  "reversal_of_id" uuid REFERENCES "journal_entry"("id"),
  "created_by" uuid NOT NULL REFERENCES "user"("id"),
  "created_at" timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS "journal_entry_date" ON "journal_entry" ("entry_date");
CREATE INDEX IF NOT EXISTS "journal_entry_type" ON "journal_entry" ("entry_type");

-- Accounting module: Journal Entry Line
CREATE TABLE IF NOT EXISTS "journal_entry_line" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  "journal_entry_id" uuid NOT NULL REFERENCES "journal_entry"("id") ON DELETE CASCADE,
  "account_code_id" uuid NOT NULL REFERENCES "account_code"("id"),
  "line_type" journal_line_type NOT NULL,
  "amount" numeric(10,2) NOT NULL,
  "description" text,
  "entity_type" varchar(50),
  "entity_id" uuid,
  "created_at" timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS "journal_entry_line_entry_id" ON "journal_entry_line" ("journal_entry_id");
CREATE INDEX IF NOT EXISTS "journal_entry_line_account" ON "journal_entry_line" ("account_code_id");

-- Lessons module: Billing Run
CREATE TABLE IF NOT EXISTS "billing_run" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  "run_date" date NOT NULL,
  "status" billing_run_status NOT NULL DEFAULT 'pending',
  "enrollments_processed" integer NOT NULL DEFAULT 0,
  "invoices_generated" integer NOT NULL DEFAULT 0,
  "total_amount" numeric(10,2) NOT NULL DEFAULT 0,
  "errors" jsonb,
  "started_at" timestamptz,
  "completed_at" timestamptz,
  "created_by" uuid REFERENCES "user"("id"),
  "created_at" timestamptz NOT NULL DEFAULT now()
);

-- Add nextBillingDate to enrollment
ALTER TABLE "enrollment" ADD COLUMN IF NOT EXISTS "next_billing_date" date;

-- Add accounting to module_config
INSERT INTO "module_config" ("slug", "name", "description", "licensed", "enabled")
VALUES ('accounting', 'Accounting', 'Chart of accounts, journal entries, and financial reports', true, false)
ON CONFLICT ("slug") DO NOTHING;

-- Seed chart of accounts
INSERT INTO "account_code" ("code", "name", "account_type", "normal_balance", "is_system") VALUES
  ('1000', 'Cash - Register Drawer', 'asset', 'debit', true),
  ('1100', 'Accounts Receivable', 'asset', 'debit', true),
  ('1200', 'Payment Clearing', 'asset', 'debit', true),
  ('1300', 'Inventory - Sale Stock', 'asset', 'debit', true),
  ('1320', 'Inventory - Parts & Supplies', 'asset', 'debit', true),
  ('2000', 'Sales Tax Payable', 'liability', 'credit', true),
  ('2110', 'Deferred Revenue - Lessons', 'liability', 'credit', true),
  ('4000', 'Sales Revenue', 'revenue', 'credit', true),
  ('4200', 'Lesson Revenue', 'revenue', 'credit', true),
  ('4300', 'Repair Revenue - Labor', 'revenue', 'credit', true),
  ('4310', 'Repair Revenue - Parts', 'revenue', 'credit', true),
  ('4900', 'Sales Discounts', 'contra_revenue', 'debit', true),
  ('4910', 'Sales Returns & Refunds', 'contra_revenue', 'debit', true),
  ('5000', 'Cost of Goods Sold', 'cogs', 'debit', true),
  ('5100', 'Repair Parts Cost', 'cogs', 'debit', true),
  ('6000', 'Cash Over / Short', 'expense', 'debit', true),
  ('6200', 'Bad Debt Expense', 'expense', 'debit', true)
ON CONFLICT ("code") DO NOTHING;
