-- accountant-export migration (wave-15)
-- Creates: coa_accounts, coa_mappings, accountant_export_jobs
-- Alters:  tenant_memberships ADD COLUMN is_accountant

CREATE TABLE IF NOT EXISTS coa_accounts (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id     UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  code          TEXT NOT NULL,
  name          TEXT NOT NULL,
  type          TEXT NOT NULL CHECK (type IN ('asset','liability','equity','revenue','expense')),
  form6111_code TEXT,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  CONSTRAINT coa_accounts_tenant_code_uniq UNIQUE (tenant_id, code)
);
--> statement-breakpoint
CREATE INDEX IF NOT EXISTS coa_accounts_tenant_idx ON coa_accounts (tenant_id);
--> statement-breakpoint

CREATE TABLE IF NOT EXISTS coa_mappings (
  tenant_id    UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  source_kind  TEXT NOT NULL,
  account_code TEXT NOT NULL,
  PRIMARY KEY (tenant_id, source_kind)
);
--> statement-breakpoint

CREATE TABLE IF NOT EXISTS accountant_export_jobs (
  id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id           UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  kind                TEXT NOT NULL CHECK (kind IN ('movement_file','form6111')),
  period_from         DATE NOT NULL,
  period_to           DATE NOT NULL,
  status              TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending','running','done','error')),
  r2_key              TEXT,
  download_expires_at TIMESTAMPTZ,
  generated_by        UUID REFERENCES users(id) ON DELETE SET NULL,
  created_at          TIMESTAMPTZ NOT NULL DEFAULT now()
);
--> statement-breakpoint
CREATE INDEX IF NOT EXISTS accountant_export_jobs_tenant_idx ON accountant_export_jobs (tenant_id, created_at DESC);
--> statement-breakpoint

ALTER TABLE tenant_memberships ADD COLUMN IF NOT EXISTS is_accountant BOOLEAN NOT NULL DEFAULT false;
