-- ============================================================
-- AVPro Suite — Complete Database Schema
-- Multi-tenant with Row-Level Security (RLS)
-- PostgreSQL 15+
-- ============================================================

-- Enable UUID extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- ─── HELPER: updated_at trigger ───────────────────────────
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- ============================================================
-- TIER 1: PLATFORM (SaaS owner level — no RLS needed)
-- ============================================================

-- Platform-level super admins (AVPro Suite staff only)
CREATE TABLE platform_admins (
  id            UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  email         TEXT UNIQUE NOT NULL,
  password_hash TEXT NOT NULL,
  name          TEXT NOT NULL,
  created_at    TIMESTAMPTZ DEFAULT NOW()
);

-- ============================================================
-- TIER 2: COMPANIES (each AV company is a tenant)
-- ============================================================

CREATE TABLE companies (
  id                  UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name                TEXT NOT NULL,
  slug                TEXT UNIQUE NOT NULL,         -- used for subdomain: slug.avprosuite.io
  email               TEXT NOT NULL,
  phone               TEXT,
  address             TEXT,
  city                TEXT,
  state               TEXT,
  zip                 TEXT,
  country             TEXT DEFAULT 'US',
  logo_url            TEXT,
  primary_color       TEXT DEFAULT '#1a56db',
  portal_domain       TEXT UNIQUE,                  -- custom domain e.g. portal.prosoundav.com
  tax_rate            NUMERIC(5,4) DEFAULT 0.0825,
  payment_terms       TEXT DEFAULT 'net_30',        -- net_30, net_15, due_on_receipt, deposit_50
  invoice_prefix      TEXT DEFAULT 'INV',
  order_prefix        TEXT DEFAULT 'ORD',
  quote_prefix        TEXT DEFAULT 'QR',
  stripe_account_id   TEXT,                         -- connected Stripe account
  square_merchant_id  TEXT,
  quickbooks_realm_id TEXT,
  smtp_host           TEXT,                          -- per-company email settings
  smtp_port           INTEGER,
  smtp_user           TEXT,
  smtp_pass_encrypted TEXT,
  smtp_from           TEXT,
  plan_id             TEXT DEFAULT 'starter',          -- starter, growth, pro, enterprise
  plan_status         TEXT DEFAULT 'trial',           -- trial, active, past_due, cancelled
  trial_ends_at       TIMESTAMPTZ DEFAULT (NOW() + INTERVAL '14 days'),
  active              BOOLEAN DEFAULT true,
  created_at          TIMESTAMPTZ DEFAULT NOW(),
  updated_at          TIMESTAMPTZ DEFAULT NOW()
);

CREATE TRIGGER companies_updated_at BEFORE UPDATE ON companies
  FOR EACH ROW EXECUTE FUNCTION set_updated_at();

-- ============================================================
-- TIER 3: USERS (belong to one company)
-- ============================================================

CREATE TYPE user_role AS ENUM (
  'super_admin',   -- company owner — full access including billing
  'admin',         -- full ops, no billing/user management
  'manager',       -- orders, inventory, staff, routes
  'staff',         -- assigned jobs + own timesheet
  'view_only',     -- read-only
  'client'         -- client portal access (their data only)
);

CREATE TABLE users (
  id                    UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  company_id            UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
  email                 TEXT NOT NULL,
  password_hash         TEXT,                       -- null if SSO only
  first_name            TEXT NOT NULL,
  last_name             TEXT NOT NULL,
  role                  user_role NOT NULL DEFAULT 'staff',
  phone                 TEXT,
  avatar_url            TEXT,
  active                BOOLEAN DEFAULT true,
  email_verified        BOOLEAN DEFAULT false,
  mfa_enabled           BOOLEAN DEFAULT false,
  mfa_secret            TEXT,                       -- encrypted TOTP secret
  failed_login_attempts INTEGER DEFAULT 0,
  locked_until          TIMESTAMPTZ,
  last_login_at         TIMESTAMPTZ,
  last_login_ip         TEXT,
  invite_token          TEXT,
  invite_expires_at     TIMESTAMPTZ,
  password_reset_token  TEXT,
  password_reset_at     TIMESTAMPTZ,
  google_id             TEXT,
  hourly_rate           NUMERIC(8,2),               -- for staff scheduling
  skills                TEXT[],                     -- ['Yamaha CL5', 'DiGiCo SD10']
  created_at            TIMESTAMPTZ DEFAULT NOW(),
  updated_at            TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(company_id, email)
);

CREATE TRIGGER users_updated_at BEFORE UPDATE ON users
  FOR EACH ROW EXECUTE FUNCTION set_updated_at();

-- Refresh tokens (stored server-side for revocation)
CREATE TABLE refresh_tokens (
  id          UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id     UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  token_hash  TEXT NOT NULL UNIQUE,
  expires_at  TIMESTAMPTZ NOT NULL,
  ip_address  TEXT,
  user_agent  TEXT,
  revoked     BOOLEAN DEFAULT false,
  created_at  TIMESTAMPTZ DEFAULT NOW()
);

-- ============================================================
-- TIER 4: CLIENTS (companies' customers — limited portal access)
-- ============================================================

CREATE TABLE clients (
  id            UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  company_id    UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
  user_id       UUID REFERENCES users(id),          -- linked portal user account
  name          TEXT NOT NULL,
  email         TEXT NOT NULL,
  phone         TEXT,
  address       TEXT,
  city          TEXT,
  state         TEXT,
  zip           TEXT,
  company_name  TEXT,
  notes         TEXT,
  tags          TEXT[],
  active        BOOLEAN DEFAULT true,
  created_at    TIMESTAMPTZ DEFAULT NOW(),
  updated_at    TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(company_id, email)
);

CREATE TRIGGER clients_updated_at BEFORE UPDATE ON clients
  FOR EACH ROW EXECUTE FUNCTION set_updated_at();

-- ============================================================
-- INVENTORY
-- ============================================================

CREATE TYPE inventory_status AS ENUM ('available', 'rented', 'maintenance', 'retired', 'lost');

CREATE TABLE inventory_categories (
  id          UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  company_id  UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
  name        TEXT NOT NULL,
  color       TEXT DEFAULT '#1a56db',
  icon        TEXT,
  created_at  TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE inventory_items (
  id                  UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  company_id          UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
  category_id         UUID REFERENCES inventory_categories(id),
  name                TEXT NOT NULL,
  description         TEXT,
  serial_number       TEXT,
  asset_tag           TEXT,
  barcode             TEXT,
  quantity_total      INTEGER NOT NULL DEFAULT 1,
  quantity_available  INTEGER NOT NULL DEFAULT 1,
  daily_rate          NUMERIC(10,2) NOT NULL DEFAULT 0,
  weekly_rate         NUMERIC(10,2),
  replacement_value   NUMERIC(10,2),
  weight_lbs          NUMERIC(6,2),
  case_number         TEXT,
  warehouse_location  TEXT,
  manufacturer        TEXT,
  model               TEXT,
  purchase_date       DATE,
  purchase_price      NUMERIC(10,2),
  notes               TEXT,
  image_url           TEXT,
  status              inventory_status DEFAULT 'available',
  active              BOOLEAN DEFAULT true,
  created_at          TIMESTAMPTZ DEFAULT NOW(),
  updated_at          TIMESTAMPTZ DEFAULT NOW()
);

CREATE TRIGGER inventory_updated_at BEFORE UPDATE ON inventory_items
  FOR EACH ROW EXECUTE FUNCTION set_updated_at();

-- Maintenance log
CREATE TABLE inventory_maintenance (
  id            UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  company_id    UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
  item_id       UUID NOT NULL REFERENCES inventory_items(id) ON DELETE CASCADE,
  type          TEXT NOT NULL,  -- 'repair', 'inspection', 'cleaning', 'calibration'
  description   TEXT,
  cost          NUMERIC(10,2),
  performed_by  TEXT,
  performed_at  DATE,
  next_due_at   DATE,
  created_by    UUID REFERENCES users(id),
  created_at    TIMESTAMPTZ DEFAULT NOW()
);

-- ============================================================
-- ORDERS, QUOTES, INVOICES
-- ============================================================

CREATE TYPE order_status AS ENUM (
  'quote_draft', 'quote_sent', 'quote_approved', 'quote_declined',
  'confirmed', 'deposit_paid', 'in_progress', 'completed',
  'invoiced', 'paid', 'cancelled', 'refunded'
);

CREATE TABLE orders (
  id                    UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  company_id            UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
  client_id             UUID REFERENCES clients(id),
  order_number          TEXT NOT NULL,
  event_name            TEXT NOT NULL,
  event_type            TEXT,
  event_date            DATE,
  load_in_date          DATE,
  load_out_date         DATE,
  load_in_time          TIME,
  show_time             TIME,
  venue_name            TEXT,
  venue_address         TEXT,
  venue_city            TEXT,
  venue_state           TEXT,
  expected_guests       INTEGER,
  status                order_status DEFAULT 'quote_draft',
  subtotal              NUMERIC(12,2) DEFAULT 0,
  tax_rate              NUMERIC(5,4),
  tax_amount            NUMERIC(12,2) DEFAULT 0,
  discount_amount       NUMERIC(12,2) DEFAULT 0,
  total                 NUMERIC(12,2) DEFAULT 0,
  deposit_amount        NUMERIC(12,2) DEFAULT 0,
  deposit_paid          BOOLEAN DEFAULT false,
  deposit_paid_at       TIMESTAMPTZ,
  balance_due           NUMERIC(12,2) DEFAULT 0,
  notes                 TEXT,
  internal_notes        TEXT,
  special_instructions  TEXT,
  signed_at             TIMESTAMPTZ,
  signed_by             TEXT,
  quote_expires_at      TIMESTAMPTZ,
  created_by            UUID REFERENCES users(id),
  assigned_to           UUID REFERENCES users(id),
  created_at            TIMESTAMPTZ DEFAULT NOW(),
  updated_at            TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(company_id, order_number)
);

CREATE TRIGGER orders_updated_at BEFORE UPDATE ON orders
  FOR EACH ROW EXECUTE FUNCTION set_updated_at();

-- Line items on an order
CREATE TABLE order_items (
  id            UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  company_id    UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
  order_id      UUID NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  item_id       UUID REFERENCES inventory_items(id),  -- null = custom line item
  name          TEXT NOT NULL,
  description   TEXT,
  type          TEXT DEFAULT 'rental',  -- rental, labor, delivery, misc
  quantity      NUMERIC(10,2) NOT NULL DEFAULT 1,
  unit          TEXT DEFAULT 'day',
  unit_price    NUMERIC(10,2) NOT NULL DEFAULT 0,
  discount_pct  NUMERIC(5,2) DEFAULT 0,
  total         NUMERIC(12,2) NOT NULL DEFAULT 0,
  sort_order    INTEGER DEFAULT 0,
  created_at    TIMESTAMPTZ DEFAULT NOW()
);

-- Invoices (can be separate from orders or linked)
CREATE TABLE invoices (
  id               UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  company_id       UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
  order_id         UUID REFERENCES orders(id),
  client_id        UUID REFERENCES clients(id),
  invoice_number   TEXT NOT NULL,
  status           TEXT DEFAULT 'draft',  -- draft, sent, viewed, partial, paid, overdue, void
  issue_date       DATE DEFAULT CURRENT_DATE,
  due_date         DATE,
  subtotal         NUMERIC(12,2) DEFAULT 0,
  tax_amount       NUMERIC(12,2) DEFAULT 0,
  discount_amount  NUMERIC(12,2) DEFAULT 0,
  total            NUMERIC(12,2) DEFAULT 0,
  amount_paid      NUMERIC(12,2) DEFAULT 0,
  balance_due      NUMERIC(12,2) DEFAULT 0,
  notes            TEXT,
  terms            TEXT,
  pdf_url          TEXT,
  sent_at          TIMESTAMPTZ,
  viewed_at        TIMESTAMPTZ,
  paid_at          TIMESTAMPTZ,
  created_by       UUID REFERENCES users(id),
  created_at       TIMESTAMPTZ DEFAULT NOW(),
  updated_at       TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(company_id, invoice_number)
);

CREATE TRIGGER invoices_updated_at BEFORE UPDATE ON invoices
  FOR EACH ROW EXECUTE FUNCTION set_updated_at();

-- ============================================================
-- PAYMENTS
-- ============================================================

CREATE TABLE payments (
  id                    UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  company_id            UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
  invoice_id            UUID REFERENCES invoices(id),
  order_id              UUID REFERENCES orders(id),
  client_id             UUID REFERENCES clients(id),
  amount                NUMERIC(12,2) NOT NULL,
  currency              TEXT DEFAULT 'usd',
  method                TEXT NOT NULL,  -- stripe, square, ach, cash, check, other
  status                TEXT DEFAULT 'pending',  -- pending, processing, completed, failed, refunded
  processor_txn_id      TEXT,           -- Stripe/Square transaction ID
  processor_ref         TEXT,
  stripe_payment_intent TEXT,
  stripe_charge_id      TEXT,
  notes                 TEXT,
  refunded_amount       NUMERIC(12,2) DEFAULT 0,
  refund_reason         TEXT,
  processed_at          TIMESTAMPTZ,
  created_by            UUID REFERENCES users(id),
  created_at            TIMESTAMPTZ DEFAULT NOW(),
  updated_at            TIMESTAMPTZ DEFAULT NOW()
);

-- Payment processor API keys (encrypted at rest)
CREATE TABLE payment_processors (
  id                  UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  company_id          UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
  processor           TEXT NOT NULL,  -- stripe, square, quickbooks, paypal, authorize_net
  api_key_encrypted   TEXT NOT NULL,
  api_secret_encrypted TEXT,
  webhook_secret_encrypted TEXT,
  account_id          TEXT,
  environment         TEXT DEFAULT 'live',  -- live, sandbox
  active              BOOLEAN DEFAULT true,
  connected_at        TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(company_id, processor)
);

-- ============================================================
-- STAFF & LABOR
-- ============================================================

CREATE TABLE staff_profiles (
  id            UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  company_id    UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
  user_id       UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  employee_id   TEXT,
  department    TEXT,
  certifications TEXT[],
  emergency_contact_name TEXT,
  emergency_contact_phone TEXT,
  shirt_size    TEXT,
  notes         TEXT,
  created_at    TIMESTAMPTZ DEFAULT NOW(),
  updated_at    TIMESTAMPTZ DEFAULT NOW()
);

-- Job assignments
CREATE TABLE job_assignments (
  id            UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  company_id    UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
  order_id      UUID NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  user_id       UUID NOT NULL REFERENCES users(id),
  role          TEXT NOT NULL,   -- 'A1', 'L1', 'Video Tech', 'Stagehand', 'Driver', etc.
  call_time     TIMESTAMPTZ,
  end_time      TIMESTAMPTZ,
  hours_scheduled NUMERIC(5,2),
  hours_actual    NUMERIC(5,2),
  rate            NUMERIC(8,2),
  total_pay       NUMERIC(10,2),
  confirmed       BOOLEAN DEFAULT false,
  notes           TEXT,
  created_at    TIMESTAMPTZ DEFAULT NOW()
);

-- Time clock entries
CREATE TABLE time_entries (
  id            UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  company_id    UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
  user_id       UUID NOT NULL REFERENCES users(id),
  order_id      UUID REFERENCES orders(id),
  clock_in      TIMESTAMPTZ NOT NULL,
  clock_out     TIMESTAMPTZ,
  break_minutes INTEGER DEFAULT 0,
  hours_worked  NUMERIC(5,2),
  rate          NUMERIC(8,2),
  total_pay     NUMERIC(10,2),
  notes         TEXT,
  approved_by   UUID REFERENCES users(id),
  approved_at   TIMESTAMPTZ,
  created_at    TIMESTAMPTZ DEFAULT NOW()
);

-- ============================================================
-- ROUTES & FLEET
-- ============================================================

CREATE TABLE vehicles (
  id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  company_id      UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
  name            TEXT NOT NULL,
  type            TEXT,  -- 'box_truck', 'sprinter', 'pickup', 'trailer'
  make            TEXT,
  model           TEXT,
  year            INTEGER,
  license_plate   TEXT,
  vin             TEXT,
  capacity_lbs    INTEGER,
  fuel_type       TEXT DEFAULT 'diesel',
  current_mileage INTEGER,
  notes           TEXT,
  active          BOOLEAN DEFAULT true,
  created_at      TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE routes (
  id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  company_id      UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
  order_id        UUID REFERENCES orders(id),
  vehicle_id      UUID REFERENCES vehicles(id),
  driver_id       UUID REFERENCES users(id),
  route_number    TEXT,
  type            TEXT DEFAULT 'delivery',  -- delivery, pickup, both
  status          TEXT DEFAULT 'planned',  -- planned, loading, in_transit, delivered, returned
  departure_time  TIMESTAMPTZ,
  arrival_time    TIMESTAMPTZ,
  stops           JSONB DEFAULT '[]',       -- [{address, order, notes, completed}]
  distance_miles  NUMERIC(7,2),
  notes           TEXT,
  created_at      TIMESTAMPTZ DEFAULT NOW(),
  updated_at      TIMESTAMPTZ DEFAULT NOW()
);

-- ============================================================
-- VENDORS
-- ============================================================

CREATE TABLE vendors (
  id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  company_id      UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
  name            TEXT NOT NULL,
  category        TEXT,  -- 'equipment_supplier', 'subcontractor', 'freight', 'maintenance'
  email           TEXT,
  phone           TEXT,
  address         TEXT,
  contact_name    TEXT,
  payment_terms   TEXT DEFAULT 'net_30',
  notes           TEXT,
  active          BOOLEAN DEFAULT true,
  created_at      TIMESTAMPTZ DEFAULT NOW(),
  updated_at      TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE purchase_orders (
  id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  company_id      UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
  vendor_id       UUID NOT NULL REFERENCES vendors(id),
  po_number       TEXT NOT NULL,
  status          TEXT DEFAULT 'draft',  -- draft, sent, received, partial, cancelled
  items           JSONB NOT NULL DEFAULT '[]',
  subtotal        NUMERIC(12,2) DEFAULT 0,
  tax_amount      NUMERIC(12,2) DEFAULT 0,
  total           NUMERIC(12,2) DEFAULT 0,
  notes           TEXT,
  expected_date   DATE,
  received_date   DATE,
  created_by      UUID REFERENCES users(id),
  created_at      TIMESTAMPTZ DEFAULT NOW(),
  updated_at      TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(company_id, po_number)
);

-- Vendor payments (AP)
CREATE TABLE vendor_payments (
  id            UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  company_id    UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
  vendor_id     UUID NOT NULL REFERENCES vendors(id),
  po_id         UUID REFERENCES purchase_orders(id),
  amount        NUMERIC(12,2) NOT NULL,
  method        TEXT DEFAULT 'ach',
  reference     TEXT,
  notes         TEXT,
  paid_at       TIMESTAMPTZ,
  created_by    UUID REFERENCES users(id),
  created_at    TIMESTAMPTZ DEFAULT NOW()
);

-- ============================================================
-- ACCOUNTING / GENERAL LEDGER
-- ============================================================

CREATE TYPE account_type AS ENUM ('asset', 'liability', 'equity', 'revenue', 'expense');

CREATE TABLE chart_of_accounts (
  id            UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  company_id    UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
  account_code  TEXT NOT NULL,
  name          TEXT NOT NULL,
  type          account_type NOT NULL,
  parent_id     UUID REFERENCES chart_of_accounts(id),
  active        BOOLEAN DEFAULT true,
  created_at    TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(company_id, account_code)
);

CREATE TABLE journal_entries (
  id            UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  company_id    UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
  entry_number  TEXT NOT NULL,
  date          DATE NOT NULL DEFAULT CURRENT_DATE,
  description   TEXT NOT NULL,
  reference     TEXT,  -- invoice number, payment ID, etc.
  type          TEXT DEFAULT 'manual',  -- manual, invoice, payment, payroll, adjustment
  posted        BOOLEAN DEFAULT false,
  posted_at     TIMESTAMPTZ,
  created_by    UUID REFERENCES users(id),
  created_at    TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE journal_entry_lines (
  id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  company_id      UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
  journal_entry_id UUID NOT NULL REFERENCES journal_entries(id) ON DELETE CASCADE,
  account_id      UUID NOT NULL REFERENCES chart_of_accounts(id),
  debit           NUMERIC(14,2) DEFAULT 0,
  credit          NUMERIC(14,2) DEFAULT 0,
  description     TEXT,
  sort_order      INTEGER DEFAULT 0
);

-- ============================================================
-- DOCUMENTS & FILES
-- ============================================================

CREATE TABLE documents (
  id            UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  company_id    UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
  order_id      UUID REFERENCES orders(id),
  client_id     UUID REFERENCES clients(id),
  name          TEXT NOT NULL,
  type          TEXT,  -- 'contract', 'rider', 'floor_plan', 'invoice_pdf', 'other'
  file_url      TEXT NOT NULL,
  file_size     INTEGER,
  mime_type     TEXT,
  signed        BOOLEAN DEFAULT false,
  signed_at     TIMESTAMPTZ,
  signed_by     TEXT,
  visible_to_client BOOLEAN DEFAULT false,
  uploaded_by   UUID REFERENCES users(id),
  created_at    TIMESTAMPTZ DEFAULT NOW()
);

-- ============================================================
-- MESSAGING (portal communications)
-- ============================================================

CREATE TABLE message_threads (
  id            UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  company_id    UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
  order_id      UUID REFERENCES orders(id),
  client_id     UUID REFERENCES clients(id),
  subject       TEXT,
  last_message_at TIMESTAMPTZ DEFAULT NOW(),
  created_at    TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE messages (
  id            UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  company_id    UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
  thread_id     UUID NOT NULL REFERENCES message_threads(id) ON DELETE CASCADE,
  sender_id     UUID NOT NULL REFERENCES users(id),
  body          TEXT NOT NULL,
  attachments   JSONB DEFAULT '[]',
  read_by       UUID[],
  created_at    TIMESTAMPTZ DEFAULT NOW()
);

-- ============================================================
-- SOCIAL MEDIA
-- ============================================================

CREATE TABLE social_accounts (
  id                UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  company_id        UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
  platform          TEXT NOT NULL,  -- facebook, instagram, twitter, linkedin, youtube, tiktok
  account_name      TEXT,
  account_id        TEXT,
  access_token_encrypted TEXT,
  refresh_token_encrypted TEXT,
  token_expires_at  TIMESTAMPTZ,
  follower_count    INTEGER DEFAULT 0,
  connected         BOOLEAN DEFAULT true,
  created_at        TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(company_id, platform)
);

CREATE TABLE social_posts (
  id                UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  company_id        UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
  caption           TEXT NOT NULL,
  media_urls        TEXT[],
  platforms         TEXT[] NOT NULL,
  status            TEXT DEFAULT 'draft',  -- draft, scheduled, published, failed
  scheduled_at      TIMESTAMPTZ,
  published_at      TIMESTAMPTZ,
  platform_post_ids JSONB DEFAULT '{}',   -- {instagram: 'xxx', facebook: 'yyy'}
  engagement        JSONB DEFAULT '{}',   -- {likes: 0, comments: 0, shares: 0}
  created_by        UUID REFERENCES users(id),
  created_at        TIMESTAMPTZ DEFAULT NOW(),
  updated_at        TIMESTAMPTZ DEFAULT NOW()
);

-- ============================================================
-- AUDIT LOG (immutable — no UPDATE/DELETE allowed)
-- ============================================================

CREATE TABLE audit_logs (
  id            UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  company_id    UUID NOT NULL REFERENCES companies(id),
  user_id       UUID REFERENCES users(id),
  user_email    TEXT,
  action        TEXT NOT NULL,
  resource      TEXT NOT NULL,  -- 'order', 'invoice', 'user', 'payment', etc.
  resource_id   TEXT,
  old_values    JSONB,
  new_values    JSONB,
  ip_address    TEXT,
  user_agent    TEXT,
  created_at    TIMESTAMPTZ DEFAULT NOW()
);

-- Audit logs are append-only
CREATE RULE no_update_audit AS ON UPDATE TO audit_logs DO INSTEAD NOTHING;
CREATE RULE no_delete_audit AS ON DELETE TO audit_logs DO INSTEAD NOTHING;

-- ============================================================
-- CALENDAR / EVENTS
-- ============================================================

CREATE TABLE calendar_events (
  id            UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  company_id    UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
  order_id      UUID REFERENCES orders(id),
  title         TEXT NOT NULL,
  type          TEXT DEFAULT 'event',  -- event, load_in, load_out, maintenance, other
  start_at      TIMESTAMPTZ NOT NULL,
  end_at        TIMESTAMPTZ,
  all_day       BOOLEAN DEFAULT false,
  venue         TEXT,
  color         TEXT,
  notes         TEXT,
  created_by    UUID REFERENCES users(id),
  created_at    TIMESTAMPTZ DEFAULT NOW()
);

-- ============================================================
-- INDEXES
-- ============================================================

-- Users
CREATE INDEX idx_users_company ON users(company_id);
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_invite_token ON users(invite_token) WHERE invite_token IS NOT NULL;

-- Orders
CREATE INDEX idx_orders_company ON orders(company_id);
CREATE INDEX idx_orders_client ON orders(client_id);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_event_date ON orders(event_date);

-- Inventory
CREATE INDEX idx_inventory_company ON inventory_items(company_id);
CREATE INDEX idx_inventory_status ON inventory_items(status);
CREATE INDEX idx_inventory_category ON inventory_items(category_id);

-- Invoices
CREATE INDEX idx_invoices_company ON invoices(company_id);
CREATE INDEX idx_invoices_client ON invoices(client_id);
CREATE INDEX idx_invoices_status ON invoices(status);

-- Payments
CREATE INDEX idx_payments_company ON payments(company_id);
CREATE INDEX idx_payments_invoice ON payments(invoice_id);

-- Audit log
CREATE INDEX idx_audit_company ON audit_logs(company_id);
CREATE INDEX idx_audit_created ON audit_logs(created_at DESC);
CREATE INDEX idx_audit_user ON audit_logs(user_id);

-- Journal entries
CREATE INDEX idx_journal_company ON journal_entries(company_id);
CREATE INDEX idx_journal_date ON journal_entries(date);

-- Messages
CREATE INDEX idx_messages_thread ON messages(thread_id);
CREATE INDEX idx_message_threads_company ON message_threads(company_id);

-- ============================================================
-- ROW-LEVEL SECURITY (RLS)
-- The single most important security feature.
-- Every query is automatically filtered by company_id
-- extracted from the JWT — zero leakage between tenants.
-- ============================================================

-- Enable RLS on all tenant tables
ALTER TABLE users                ENABLE ROW LEVEL SECURITY;
ALTER TABLE clients              ENABLE ROW LEVEL SECURITY;
ALTER TABLE inventory_categories ENABLE ROW LEVEL SECURITY;
ALTER TABLE inventory_items      ENABLE ROW LEVEL SECURITY;
ALTER TABLE inventory_maintenance ENABLE ROW LEVEL SECURITY;
ALTER TABLE orders               ENABLE ROW LEVEL SECURITY;
ALTER TABLE order_items          ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices             ENABLE ROW LEVEL SECURITY;
ALTER TABLE payments             ENABLE ROW LEVEL SECURITY;
ALTER TABLE payment_processors   ENABLE ROW LEVEL SECURITY;
ALTER TABLE staff_profiles       ENABLE ROW LEVEL SECURITY;
ALTER TABLE job_assignments      ENABLE ROW LEVEL SECURITY;
ALTER TABLE time_entries         ENABLE ROW LEVEL SECURITY;
ALTER TABLE vehicles             ENABLE ROW LEVEL SECURITY;
ALTER TABLE routes               ENABLE ROW LEVEL SECURITY;
ALTER TABLE vendors              ENABLE ROW LEVEL SECURITY;
ALTER TABLE purchase_orders      ENABLE ROW LEVEL SECURITY;
ALTER TABLE vendor_payments      ENABLE ROW LEVEL SECURITY;
ALTER TABLE chart_of_accounts    ENABLE ROW LEVEL SECURITY;
ALTER TABLE journal_entries      ENABLE ROW LEVEL SECURITY;
ALTER TABLE journal_entry_lines  ENABLE ROW LEVEL SECURITY;
ALTER TABLE documents            ENABLE ROW LEVEL SECURITY;
ALTER TABLE message_threads      ENABLE ROW LEVEL SECURITY;
ALTER TABLE messages             ENABLE ROW LEVEL SECURITY;
ALTER TABLE social_accounts      ENABLE ROW LEVEL SECURITY;
ALTER TABLE social_posts         ENABLE ROW LEVEL SECURITY;
ALTER TABLE audit_logs           ENABLE ROW LEVEL SECURITY;
ALTER TABLE calendar_events      ENABLE ROW LEVEL SECURITY;

-- Create the app DB role used by the Node.js backend
CREATE ROLE avpro_app LOGIN PASSWORD 'change_in_production';
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO avpro_app;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO avpro_app;

-- RLS policy template (applied to every tenant table):
-- The app sets a session variable from the JWT on every request.
-- current_setting('app.company_id') returns '' if not set, so
-- we also check for the bypass role used in migrations.

CREATE OR REPLACE FUNCTION get_current_company_id() RETURNS UUID AS $$
BEGIN
  RETURN NULLIF(current_setting('app.company_id', true), '')::UUID;
EXCEPTION WHEN OTHERS THEN
  RETURN NULL;
END;
$$ LANGUAGE plpgsql STABLE;

CREATE OR REPLACE FUNCTION get_current_user_id() RETURNS UUID AS $$
BEGIN
  RETURN NULLIF(current_setting('app.user_id', true), '')::UUID;
EXCEPTION WHEN OTHERS THEN
  RETURN NULL;
END;
$$ LANGUAGE plpgsql STABLE;

CREATE OR REPLACE FUNCTION get_current_user_role() RETURNS TEXT AS $$
BEGIN
  RETURN NULLIF(current_setting('app.user_role', true), '');
EXCEPTION WHEN OTHERS THEN
  RETURN NULL;
END;
$$ LANGUAGE plpgsql STABLE;

-- Apply isolation policy to every tenant table
DO $$
DECLARE
  tbl TEXT;
  tables TEXT[] := ARRAY[
    'users','clients','inventory_categories','inventory_items',
    'inventory_maintenance','orders','order_items','invoices',
    'payments','payment_processors','staff_profiles','job_assignments',
    'time_entries','vehicles','routes','vendors','purchase_orders',
    'vendor_payments','chart_of_accounts','journal_entries',
    'journal_entry_lines','documents','message_threads','messages',
    'social_accounts','social_posts','audit_logs','calendar_events'
  ];
BEGIN
  FOREACH tbl IN ARRAY tables LOOP
    EXECUTE format('
      CREATE POLICY tenant_isolation ON %I
        USING (company_id = get_current_company_id())
        WITH CHECK (company_id = get_current_company_id());
    ', tbl);
  END LOOP;
END;
$$;

-- Staff can only see their own time entries
CREATE POLICY staff_own_time ON time_entries
  AS RESTRICTIVE
  USING (
    get_current_user_role() IN ('super_admin','admin','manager')
    OR user_id = get_current_user_id()
  );

-- Clients can only see their own data in orders/invoices/documents
CREATE POLICY client_own_orders ON orders
  AS RESTRICTIVE
  USING (
    get_current_user_role() != 'client'
    OR client_id IN (
      SELECT id FROM clients WHERE user_id = get_current_user_id()
    )
  );

CREATE POLICY client_own_invoices ON invoices
  AS RESTRICTIVE
  USING (
    get_current_user_role() != 'client'
    OR client_id IN (
      SELECT id FROM clients WHERE user_id = get_current_user_id()
    )
  );

CREATE POLICY client_own_documents ON documents
  AS RESTRICTIVE
  USING (
    get_current_user_role() != 'client'
    OR (
      visible_to_client = true
      AND client_id IN (
        SELECT id FROM clients WHERE user_id = get_current_user_id()
      )
    )
  );

-- ============================================================
-- ACCOUNTING EXTENSION (appended)
-- ============================================================

-- ============================================================

-- ─── FISCAL YEARS & PERIODS ───────────────────────────────────
CREATE TABLE fiscal_years (
  id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  company_id      UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
  name            TEXT NOT NULL,           -- "FY 2026"
  start_date      DATE NOT NULL,
  end_date        DATE NOT NULL,
  closed          BOOLEAN DEFAULT false,
  closed_at       TIMESTAMPTZ,
  closed_by       UUID REFERENCES users(id),
  created_at      TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE accounting_periods (
  id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  company_id      UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
  fiscal_year_id  UUID NOT NULL REFERENCES fiscal_years(id),
  name            TEXT NOT NULL,           -- "April 2026"
  period_number   INTEGER NOT NULL,        -- 1-12
  start_date      DATE NOT NULL,
  end_date        DATE NOT NULL,
  closed          BOOLEAN DEFAULT false,
  closed_at       TIMESTAMPTZ,
  created_at      TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(company_id, fiscal_year_id, period_number)
);

-- ─── ENHANCED CHART OF ACCOUNTS ───────────────────────────────
-- Drop and recreate with more fields
ALTER TABLE chart_of_accounts
  ADD COLUMN IF NOT EXISTS description        TEXT,
  ADD COLUMN IF NOT EXISTS normal_balance     TEXT DEFAULT 'debit', -- debit | credit
  ADD COLUMN IF NOT EXISTS sub_type           TEXT,
  -- asset sub_types: cash, accounts_receivable, inventory, fixed_asset, other_asset
  -- liability: accounts_payable, credit_card, sales_tax_payable, payroll_liability
  -- equity: owners_equity, retained_earnings, drawings
  -- revenue: service_revenue, rental_revenue, other_income
  -- expense: cogs, payroll, rent, utilities, marketing, depreciation, other
  ADD COLUMN IF NOT EXISTS bank_account       BOOLEAN DEFAULT false,
  ADD COLUMN IF NOT EXISTS sort_order         INTEGER DEFAULT 0,
  ADD COLUMN IF NOT EXISTS opening_balance    NUMERIC(14,2) DEFAULT 0,
  ADD COLUMN IF NOT EXISTS opening_balance_date DATE;

-- ─── BANK ACCOUNTS ────────────────────────────────────────────
CREATE TABLE bank_accounts (
  id                  UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  company_id          UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
  account_id          UUID NOT NULL REFERENCES chart_of_accounts(id),
  name                TEXT NOT NULL,
  institution         TEXT,
  account_number_last4 TEXT,
  routing_number_encrypted TEXT,
  account_type        TEXT DEFAULT 'checking', -- checking, savings, credit_card
  current_balance     NUMERIC(14,2) DEFAULT 0,
  last_reconciled_date DATE,
  last_reconciled_balance NUMERIC(14,2),
  active              BOOLEAN DEFAULT true,
  created_at          TIMESTAMPTZ DEFAULT NOW(),
  updated_at          TIMESTAMPTZ DEFAULT NOW()
);

-- ─── BANK TRANSACTIONS (for reconciliation) ───────────────────
CREATE TABLE bank_transactions (
  id                  UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  company_id          UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
  bank_account_id     UUID NOT NULL REFERENCES bank_accounts(id),
  date                DATE NOT NULL,
  description         TEXT NOT NULL,
  amount              NUMERIC(14,2) NOT NULL,  -- positive = deposit, negative = withdrawal
  type                TEXT,                    -- debit | credit
  reference           TEXT,
  matched_payment_id  UUID REFERENCES payments(id),
  matched_expense_id  UUID,                    -- references expenses.id
  reconciled          BOOLEAN DEFAULT false,
  reconciled_at       TIMESTAMPTZ,
  imported_at         TIMESTAMPTZ DEFAULT NOW(),
  created_at          TIMESTAMPTZ DEFAULT NOW()
);

-- ─── EXPENSES ─────────────────────────────────────────────────
CREATE TABLE expenses (
  id                  UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  company_id          UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
  expense_number      TEXT NOT NULL,
  vendor_id           UUID REFERENCES vendors(id),
  vendor_name         TEXT,                    -- for one-off vendors
  account_id          UUID REFERENCES chart_of_accounts(id),
  bank_account_id     UUID REFERENCES bank_accounts(id),
  date                DATE NOT NULL DEFAULT CURRENT_DATE,
  description         TEXT NOT NULL,
  category            TEXT,
  amount              NUMERIC(12,2) NOT NULL,
  tax_amount          NUMERIC(12,2) DEFAULT 0,
  total               NUMERIC(12,2) NOT NULL,
  payment_method      TEXT DEFAULT 'check',    -- check, ach, credit_card, cash, other
  reference           TEXT,
  receipt_url         TEXT,
  recurring           BOOLEAN DEFAULT false,
  recurring_frequency TEXT,                    -- monthly, weekly, quarterly, annually
  next_occurrence     DATE,
  billable            BOOLEAN DEFAULT false,   -- can be billed to client
  client_id           UUID REFERENCES clients(id),
  journal_entry_id    UUID,                    -- set after posting
  posted              BOOLEAN DEFAULT false,
  notes               TEXT,
  created_by          UUID REFERENCES users(id),
  approved_by         UUID REFERENCES users(id),
  approved_at         TIMESTAMPTZ,
  created_at          TIMESTAMPTZ DEFAULT NOW(),
  updated_at          TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(company_id, expense_number)
);

-- Expense line items (for multi-category expenses)
CREATE TABLE expense_lines (
  id            UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  company_id    UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
  expense_id    UUID NOT NULL REFERENCES expenses(id) ON DELETE CASCADE,
  account_id    UUID NOT NULL REFERENCES chart_of_accounts(id),
  description   TEXT,
  amount        NUMERIC(12,2) NOT NULL,
  sort_order    INTEGER DEFAULT 0
);

-- ─── VENDOR BILLS (AP) ────────────────────────────────────────
CREATE TABLE vendor_bills (
  id                  UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  company_id          UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
  vendor_id           UUID NOT NULL REFERENCES vendors(id),
  bill_number         TEXT NOT NULL,
  vendor_bill_number  TEXT,                    -- vendor's own reference
  status              TEXT DEFAULT 'draft',    -- draft, open, partial, paid, overdue, void
  issue_date          DATE NOT NULL DEFAULT CURRENT_DATE,
  due_date            DATE,
  subtotal            NUMERIC(12,2) DEFAULT 0,
  tax_amount          NUMERIC(12,2) DEFAULT 0,
  total               NUMERIC(12,2) NOT NULL,
  amount_paid         NUMERIC(12,2) DEFAULT 0,
  balance_due         NUMERIC(12,2) NOT NULL,
  notes               TEXT,
  journal_entry_id    UUID,
  posted              BOOLEAN DEFAULT false,
  created_by          UUID REFERENCES users(id),
  created_at          TIMESTAMPTZ DEFAULT NOW(),
  updated_at          TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(company_id, bill_number)
);

CREATE TABLE vendor_bill_lines (
  id            UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  company_id    UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
  bill_id       UUID NOT NULL REFERENCES vendor_bills(id) ON DELETE CASCADE,
  account_id    UUID REFERENCES chart_of_accounts(id),
  description   TEXT NOT NULL,
  quantity      NUMERIC(10,2) DEFAULT 1,
  unit_price    NUMERIC(10,2) NOT NULL,
  total         NUMERIC(12,2) NOT NULL,
  sort_order    INTEGER DEFAULT 0
);

-- ─── PAYROLL ──────────────────────────────────────────────────
CREATE TABLE payroll_runs (
  id                  UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  company_id          UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
  run_number          TEXT NOT NULL,
  period_start        DATE NOT NULL,
  period_end          DATE NOT NULL,
  pay_date            DATE NOT NULL,
  status              TEXT DEFAULT 'draft',    -- draft, approved, paid, void
  total_gross         NUMERIC(12,2) DEFAULT 0,
  total_deductions    NUMERIC(12,2) DEFAULT 0,
  total_net           NUMERIC(12,2) DEFAULT 0,
  total_employer_tax  NUMERIC(12,2) DEFAULT 0,
  notes               TEXT,
  journal_entry_id    UUID,
  approved_by         UUID REFERENCES users(id),
  approved_at         TIMESTAMPTZ,
  created_by          UUID REFERENCES users(id),
  created_at          TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(company_id, run_number)
);

CREATE TABLE payroll_entries (
  id                  UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  company_id          UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
  payroll_run_id      UUID NOT NULL REFERENCES payroll_runs(id) ON DELETE CASCADE,
  user_id             UUID NOT NULL REFERENCES users(id),
  regular_hours       NUMERIC(6,2) DEFAULT 0,
  overtime_hours      NUMERIC(6,2) DEFAULT 0,
  regular_rate        NUMERIC(8,2) NOT NULL,
  overtime_rate       NUMERIC(8,2),
  gross_pay           NUMERIC(12,2) NOT NULL,
  federal_tax         NUMERIC(10,2) DEFAULT 0,
  state_tax           NUMERIC(10,2) DEFAULT 0,
  social_security     NUMERIC(10,2) DEFAULT 0,
  medicare            NUMERIC(10,2) DEFAULT 0,
  other_deductions    NUMERIC(10,2) DEFAULT 0,
  net_pay             NUMERIC(12,2) NOT NULL,
  -- Employer side
  employer_ss         NUMERIC(10,2) DEFAULT 0,
  employer_medicare   NUMERIC(10,2) DEFAULT 0,
  employer_futa       NUMERIC(10,2) DEFAULT 0,
  employer_suta       NUMERIC(10,2) DEFAULT 0,
  payment_method      TEXT DEFAULT 'direct_deposit',
  notes               TEXT,
  created_at          TIMESTAMPTZ DEFAULT NOW()
);

-- ─── TAX RATES & TRACKING ─────────────────────────────────────
CREATE TABLE tax_rates (
  id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  company_id      UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
  name            TEXT NOT NULL,           -- "Texas State Sales Tax"
  rate            NUMERIC(6,4) NOT NULL,   -- 0.0825 = 8.25%
  type            TEXT DEFAULT 'sales',    -- sales, use, service
  jurisdiction    TEXT,                    -- "TX", "Austin, TX"
  account_id      UUID REFERENCES chart_of_accounts(id),
  active          BOOLEAN DEFAULT true,
  created_at      TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE tax_payments (
  id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  company_id      UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
  tax_rate_id     UUID REFERENCES tax_rates(id),
  period_start    DATE NOT NULL,
  period_end      DATE NOT NULL,
  amount_collected NUMERIC(12,2) NOT NULL,
  amount_paid     NUMERIC(12,2) DEFAULT 0,
  due_date        DATE,
  paid_date       DATE,
  status          TEXT DEFAULT 'pending',  -- pending, paid, late
  reference       TEXT,
  notes           TEXT,
  created_by      UUID REFERENCES users(id),
  created_at      TIMESTAMPTZ DEFAULT NOW()
);

-- ─── RECONCILIATION SESSIONS ──────────────────────────────────
CREATE TABLE reconciliation_sessions (
  id                      UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  company_id              UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
  bank_account_id         UUID NOT NULL REFERENCES bank_accounts(id),
  statement_date          DATE NOT NULL,
  statement_balance       NUMERIC(14,2) NOT NULL,
  cleared_balance         NUMERIC(14,2) DEFAULT 0,
  difference              NUMERIC(14,2) DEFAULT 0,
  status                  TEXT DEFAULT 'in_progress', -- in_progress, completed
  completed_at            TIMESTAMPTZ,
  completed_by            UUID REFERENCES users(id),
  created_at              TIMESTAMPTZ DEFAULT NOW()
);

-- ─── BUDGETS ──────────────────────────────────────────────────
CREATE TABLE budgets (
  id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  company_id      UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
  fiscal_year_id  UUID REFERENCES fiscal_years(id),
  name            TEXT NOT NULL,
  account_id      UUID NOT NULL REFERENCES chart_of_accounts(id),
  jan NUMERIC(12,2) DEFAULT 0, feb NUMERIC(12,2) DEFAULT 0,
  mar NUMERIC(12,2) DEFAULT 0, apr NUMERIC(12,2) DEFAULT 0,
  may NUMERIC(12,2) DEFAULT 0, jun NUMERIC(12,2) DEFAULT 0,
  jul NUMERIC(12,2) DEFAULT 0, aug NUMERIC(12,2) DEFAULT 0,
  sep NUMERIC(12,2) DEFAULT 0, oct NUMERIC(12,2) DEFAULT 0,
  nov NUMERIC(12,2) DEFAULT 0, dec NUMERIC(12,2) DEFAULT 0,
  annual_total    NUMERIC(14,2) GENERATED ALWAYS AS
    (jan+feb+mar+apr+may+jun+jul+aug+sep+oct+nov+dec) STORED,
  created_at      TIMESTAMPTZ DEFAULT NOW(),
  updated_at      TIMESTAMPTZ DEFAULT NOW()
);

-- ─── AUTO-POSTING TRIGGERS ────────────────────────────────────
-- When a payment is marked completed, auto-post the journal entry

CREATE OR REPLACE FUNCTION auto_post_payment()
RETURNS TRIGGER AS $$
DECLARE
  v_ar_account    UUID;
  v_cash_account  UUID;
  v_entry_id      UUID;
  v_entry_number  TEXT;
  v_count         INTEGER;
BEGIN
  -- Only fire on status change to 'completed'
  IF NEW.status = 'completed' AND (OLD.status IS NULL OR OLD.status != 'completed') THEN

    -- Get AR and Cash account IDs for this company
    SELECT id INTO v_ar_account FROM chart_of_accounts
      WHERE company_id = NEW.company_id AND account_code = '1100' LIMIT 1;
    SELECT id INTO v_cash_account FROM chart_of_accounts
      WHERE company_id = NEW.company_id AND account_code = '1000' LIMIT 1;

    IF v_ar_account IS NULL OR v_cash_account IS NULL THEN
      RETURN NEW; -- Skip if CoA not set up
    END IF;

    -- Generate entry number
    SELECT COUNT(*)+1 INTO v_count FROM journal_entries WHERE company_id = NEW.company_id;
    v_entry_number := 'JE-' || LPAD(v_count::TEXT, 5, '0');
    v_entry_id := uuid_generate_v4();

    -- Create journal entry: DR Cash / CR Accounts Receivable
    INSERT INTO journal_entries (id, company_id, entry_number, date, description, reference, type, posted)
    VALUES (
      v_entry_id, NEW.company_id, v_entry_number, CURRENT_DATE,
      'Payment received - ' || COALESCE(NEW.notes, NEW.method),
      NEW.id::TEXT, 'payment', true
    );

    -- Debit Cash
    INSERT INTO journal_entry_lines (company_id, journal_entry_id, account_id, debit, credit, description)
    VALUES (NEW.company_id, v_entry_id, v_cash_account, NEW.amount, 0, 'Cash received');

    -- Credit AR
    INSERT INTO journal_entry_lines (company_id, journal_entry_id, account_id, debit, credit, description)
    VALUES (NEW.company_id, v_entry_id, v_ar_account, 0, NEW.amount, 'Invoice payment');

  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_auto_post_payment
  AFTER INSERT OR UPDATE ON payments
  FOR EACH ROW EXECUTE FUNCTION auto_post_payment();

-- ─── AUTO-POST INVOICE CREATION ───────────────────────────────
CREATE OR REPLACE FUNCTION auto_post_invoice()
RETURNS TRIGGER AS $$
DECLARE
  v_ar_account    UUID;
  v_rev_account   UUID;
  v_tax_account   UUID;
  v_entry_id      UUID;
  v_entry_number  TEXT;
  v_count         INTEGER;
BEGIN
  IF NEW.status IN ('sent','paid') AND (OLD.status = 'draft' OR OLD.status IS NULL) THEN

    SELECT id INTO v_ar_account FROM chart_of_accounts
      WHERE company_id = NEW.company_id AND account_code = '1100' LIMIT 1;
    SELECT id INTO v_rev_account FROM chart_of_accounts
      WHERE company_id = NEW.company_id AND account_code = '4000' LIMIT 1;
    SELECT id INTO v_tax_account FROM chart_of_accounts
      WHERE company_id = NEW.company_id AND account_code = '2100' LIMIT 1;

    IF v_ar_account IS NULL THEN RETURN NEW; END IF;

    SELECT COUNT(*)+1 INTO v_count FROM journal_entries WHERE company_id = NEW.company_id;
    v_entry_number := 'JE-' || LPAD(v_count::TEXT, 5, '0');
    v_entry_id := uuid_generate_v4();

    INSERT INTO journal_entries (id, company_id, entry_number, date, description, reference, type, posted)
    VALUES (v_entry_id, NEW.company_id, v_entry_number, CURRENT_DATE,
      'Invoice ' || NEW.invoice_number, NEW.id::TEXT, 'invoice', true);

    -- Debit AR for full invoice amount
    INSERT INTO journal_entry_lines (company_id, journal_entry_id, account_id, debit, credit, description)
    VALUES (NEW.company_id, v_entry_id, v_ar_account, NEW.total, 0, 'Invoice ' || NEW.invoice_number);

    -- Credit Revenue for subtotal
    IF v_rev_account IS NOT NULL THEN
      INSERT INTO journal_entry_lines (company_id, journal_entry_id, account_id, debit, credit, description)
      VALUES (NEW.company_id, v_entry_id, v_rev_account, 0, NEW.subtotal, 'Revenue');
    END IF;

    -- Credit Sales Tax Payable
    IF v_tax_account IS NOT NULL AND NEW.tax_amount > 0 THEN
      INSERT INTO journal_entry_lines (company_id, journal_entry_id, account_id, debit, credit, description)
      VALUES (NEW.company_id, v_entry_id, v_tax_account, 0, NEW.tax_amount, 'Sales tax collected');
    END IF;

  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_auto_post_invoice
  AFTER INSERT OR UPDATE ON invoices
  FOR EACH ROW EXECUTE FUNCTION auto_post_invoice();

-- ─── USEFUL ACCOUNTING VIEWS ──────────────────────────────────

-- Trial Balance
CREATE OR REPLACE VIEW v_trial_balance AS
SELECT
  coa.company_id,
  coa.account_code,
  coa.name as account_name,
  coa.type as account_type,
  coa.normal_balance,
  COALESCE(SUM(jel.debit),  0) as total_debits,
  COALESCE(SUM(jel.credit), 0) as total_credits,
  CASE
    WHEN coa.normal_balance = 'debit'
    THEN COALESCE(SUM(jel.debit),0) - COALESCE(SUM(jel.credit),0)
    ELSE COALESCE(SUM(jel.credit),0) - COALESCE(SUM(jel.debit),0)
  END as balance
FROM chart_of_accounts coa
LEFT JOIN journal_entry_lines jel ON jel.account_id = coa.id
LEFT JOIN journal_entries je ON je.id = jel.journal_entry_id AND je.posted = true
WHERE coa.active = true
GROUP BY coa.company_id, coa.id, coa.account_code, coa.name, coa.type, coa.normal_balance
ORDER BY coa.account_code;

-- P&L View
CREATE OR REPLACE VIEW v_profit_loss AS
SELECT
  company_id,
  account_type,
  sub_type,
  account_code,
  account_name,
  balance
FROM v_trial_balance
WHERE account_type IN ('revenue', 'expense');

-- Balance Sheet View
CREATE OR REPLACE VIEW v_balance_sheet AS
SELECT
  company_id,
  account_type,
  sub_type,
  account_code,
  account_name,
  balance
FROM v_trial_balance
WHERE account_type IN ('asset', 'liability', 'equity');

-- AP Aging view
CREATE OR REPLACE VIEW v_ap_aging AS
SELECT
  vb.company_id,
  v.name as vendor_name,
  vb.bill_number,
  vb.total,
  vb.balance_due,
  vb.due_date,
  vb.status,
  CURRENT_DATE - vb.due_date as days_overdue,
  CASE
    WHEN vb.due_date >= CURRENT_DATE THEN 'current'
    WHEN CURRENT_DATE - vb.due_date <= 30 THEN '1_30'
    WHEN CURRENT_DATE - vb.due_date <= 60 THEN '31_60'
    WHEN CURRENT_DATE - vb.due_date <= 90 THEN '61_90'
    ELSE '90_plus'
  END as aging_bucket
FROM vendor_bills vb
JOIN vendors v ON v.id = vb.vendor_id
WHERE vb.balance_due > 0 AND vb.status != 'void';

-- Enable RLS on all new tables
ALTER TABLE fiscal_years           ENABLE ROW LEVEL SECURITY;
ALTER TABLE accounting_periods     ENABLE ROW LEVEL SECURITY;
ALTER TABLE bank_accounts          ENABLE ROW LEVEL SECURITY;
ALTER TABLE bank_transactions      ENABLE ROW LEVEL SECURITY;
ALTER TABLE expenses               ENABLE ROW LEVEL SECURITY;
ALTER TABLE expense_lines          ENABLE ROW LEVEL SECURITY;
ALTER TABLE vendor_bills           ENABLE ROW LEVEL SECURITY;
ALTER TABLE vendor_bill_lines      ENABLE ROW LEVEL SECURITY;
ALTER TABLE payroll_runs           ENABLE ROW LEVEL SECURITY;
ALTER TABLE payroll_entries        ENABLE ROW LEVEL SECURITY;
ALTER TABLE tax_rates              ENABLE ROW LEVEL SECURITY;
ALTER TABLE tax_payments           ENABLE ROW LEVEL SECURITY;
ALTER TABLE reconciliation_sessions ENABLE ROW LEVEL SECURITY;
ALTER TABLE budgets                ENABLE ROW LEVEL SECURITY;

-- Apply tenant isolation to all new tables
DO $$
DECLARE
  tbl TEXT;
  tables TEXT[] := ARRAY[
    'fiscal_years','accounting_periods','bank_accounts','bank_transactions',
    'expenses','expense_lines','vendor_bills','vendor_bill_lines',
    'payroll_runs','payroll_entries','tax_rates','tax_payments',
    'reconciliation_sessions','budgets'
  ];
BEGIN
  FOREACH tbl IN ARRAY tables LOOP
    EXECUTE format('
      CREATE POLICY tenant_isolation ON %I
        USING (company_id = get_current_company_id())
        WITH CHECK (company_id = get_current_company_id());
    ', tbl);
  END LOOP;
END;
$$;

-- Grant permissions to app role
GRANT SELECT, INSERT, UPDATE, DELETE ON
  fiscal_years, accounting_periods, bank_accounts, bank_transactions,
  expenses, expense_lines, vendor_bills, vendor_bill_lines,
  payroll_runs, payroll_entries, tax_rates, tax_payments,
  reconciliation_sessions, budgets
TO avpro_app;

GRANT SELECT ON v_trial_balance, v_profit_loss, v_balance_sheet, v_ap_aging TO avpro_app;

-- ============================================================
-- BRANDING & BILLING COLUMNS (appended)
-- ============================================================

ALTER TABLE companies
  ADD COLUMN IF NOT EXISTS plan_id                TEXT DEFAULT 'starter',
  ADD COLUMN IF NOT EXISTS plan_status            TEXT DEFAULT 'trial',
  ADD COLUMN IF NOT EXISTS trial_ends_at          TIMESTAMPTZ DEFAULT NOW() + INTERVAL '14 days',
  ADD COLUMN IF NOT EXISTS stripe_customer_id     TEXT UNIQUE,
  ADD COLUMN IF NOT EXISTS stripe_sub_id          TEXT UNIQUE,
  ADD COLUMN IF NOT EXISTS current_period_end     TIMESTAMPTZ,
  ADD COLUMN IF NOT EXISTS cancel_at_period_end   BOOLEAN DEFAULT false,
  ADD COLUMN IF NOT EXISTS billing_email          TEXT,
  ADD COLUMN IF NOT EXISTS custom_domain          TEXT UNIQUE,
  ADD COLUMN IF NOT EXISTS domain_verified        BOOLEAN DEFAULT false,
  ADD COLUMN IF NOT EXISTS domain_verified_at     TIMESTAMPTZ,
  ADD COLUMN IF NOT EXISTS logo_dark_url          TEXT,
  ADD COLUMN IF NOT EXISTS favicon_url            TEXT,
  ADD COLUMN IF NOT EXISTS brand_primary          TEXT DEFAULT '#1a56db',
  ADD COLUMN IF NOT EXISTS brand_accent           TEXT DEFAULT '#5b9ef9',
  ADD COLUMN IF NOT EXISTS brand_bg               TEXT DEFAULT '#ffffff',
  ADD COLUMN IF NOT EXISTS brand_text             TEXT DEFAULT '#0f172a',
  ADD COLUMN IF NOT EXISTS portal_title           TEXT,
  ADD COLUMN IF NOT EXISTS portal_welcome         TEXT,
  ADD COLUMN IF NOT EXISTS portal_tagline         TEXT,
  ADD COLUMN IF NOT EXISTS email_from_name        TEXT,
  ADD COLUMN IF NOT EXISTS email_reply_to         TEXT,
  ADD COLUMN IF NOT EXISTS email_footer           TEXT,
  ADD COLUMN IF NOT EXISTS invoice_footer         TEXT,
  ADD COLUMN IF NOT EXISTS invoice_terms          TEXT DEFAULT 'Payment due within 30 days.',
  ADD COLUMN IF NOT EXISTS social_facebook        TEXT,
  ADD COLUMN IF NOT EXISTS social_instagram       TEXT,
  ADD COLUMN IF NOT EXISTS social_linkedin        TEXT,
  ADD COLUMN IF NOT EXISTS social_twitter         TEXT,
  ADD COLUMN IF NOT EXISTS social_youtube         TEXT,
  ADD COLUMN IF NOT EXISTS social_website         TEXT,
  ADD COLUMN IF NOT EXISTS venue_lat              NUMERIC(10,8),
  ADD COLUMN IF NOT EXISTS venue_lng              NUMERIC(11,8);

CREATE TABLE IF NOT EXISTS billing_events (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  company_id UUID REFERENCES companies(id),
  event_type TEXT NOT NULL, stripe_event_id TEXT, amount NUMERIC(10,2),
  currency TEXT DEFAULT 'usd', plan_id TEXT, description TEXT, metadata JSONB,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS branding_history (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
  changed_by UUID REFERENCES users(id),
  changed_at TIMESTAMPTZ DEFAULT NOW(),
  field TEXT NOT NULL, old_value TEXT, new_value TEXT
);
ALTER TABLE branding_history ENABLE ROW LEVEL SECURITY;
CREATE POLICY IF NOT EXISTS branding_history_tenant ON branding_history USING (company_id = get_current_company_id()) WITH CHECK (company_id = get_current_company_id());

CREATE TABLE IF NOT EXISTS social_connections (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
  platform TEXT NOT NULL, account_id TEXT, account_name TEXT, account_url TEXT,
  access_token TEXT, refresh_token TEXT, token_expires_at TIMESTAMPTZ,
  scopes TEXT[], active BOOLEAN DEFAULT true,
  connected_at TIMESTAMPTZ DEFAULT NOW(), connected_by UUID REFERENCES users(id),
  UNIQUE(company_id, platform)
);
ALTER TABLE social_connections ENABLE ROW LEVEL SECURITY;
CREATE POLICY IF NOT EXISTS social_connections_tenant ON social_connections USING (company_id = get_current_company_id()) WITH CHECK (company_id = get_current_company_id());

CREATE INDEX IF NOT EXISTS idx_companies_plan_status ON companies(plan_status);
CREATE INDEX IF NOT EXISTS idx_companies_slug ON companies(slug) WHERE slug IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_companies_custom_domain ON companies(custom_domain) WHERE custom_domain IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_billing_events_company ON billing_events(company_id);

-- ============================================================
-- MESSAGING PATCHES (appended)
-- ============================================================

ALTER TABLE message_threads
  ADD COLUMN IF NOT EXISTS created_by          UUID REFERENCES users(id),
  ADD COLUMN IF NOT EXISTS last_message_preview TEXT;

ALTER TABLE messages
  ADD COLUMN IF NOT EXISTS content TEXT;

-- Sync content from body for any existing rows
UPDATE messages SET content = body WHERE content IS NULL AND body IS NOT NULL;

CREATE TABLE IF NOT EXISTS thread_participants (
  id         UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  thread_id  UUID NOT NULL REFERENCES message_threads(id) ON DELETE CASCADE,
  user_id    UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  joined_at  TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(thread_id, user_id)
);

CREATE TABLE IF NOT EXISTS message_thread_reads (
  thread_id    UUID NOT NULL REFERENCES message_threads(id) ON DELETE CASCADE,
  user_id      UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  last_read_at TIMESTAMPTZ DEFAULT NOW(),
  PRIMARY KEY(thread_id, user_id)
);

ALTER TABLE thread_participants ENABLE ROW LEVEL SECURITY;
CREATE POLICY IF NOT EXISTS tp_tenant ON thread_participants
  USING (EXISTS (SELECT 1 FROM message_threads mt WHERE mt.id = thread_id AND mt.company_id = get_current_company_id()));

-- Fix seed.ts column name: subscription_plan → plan_id, subscription_status → plan_status
-- (handled by ALTER TABLE ADD COLUMN above — both sets of columns now exist)