-- Migration: Create cost_schedules table and register for sync
-- Date: 2025-10-21

-- Create table if not exists
CREATE TABLE IF NOT EXISTS cost_schedules (
  cost_schedule_id       uuid PRIMARY KEY,
  case_id                uuid,
  schedule_number        text,
  schedule_date          date,
  status                 text,
  total_amount           numeric(10,2) DEFAULT 0 NOT NULL,
  items                  jsonb,
  client_name            text,
  client_tin             text,
  taxcore_invoice_number text,
  taxcore_submitted_at   timestamptz,
  org_id                 uuid,
  isDeleted              boolean DEFAULT FALSE NOT NULL,
  updatedAt              timestamptz DEFAULT now() NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_cost_schedules_case_id ON cost_schedules (case_id);
CREATE INDEX IF NOT EXISTS idx_cost_schedules_org_id ON cost_schedules (org_id);
CREATE INDEX IF NOT EXISTS idx_cost_schedules_status ON cost_schedules (status);

-- Register for IndexedDB sync if not present
INSERT INTO tenant_tables (table_name, definition, isDeleted, updatedAt)
SELECT 
  'cost_schedules',
  '{
    "columns": [
      {"name": "cost_schedule_id", "type": "uuid", "primary": true},
      {"name": "case_id", "type": "uuid"},
      {"name": "schedule_number", "type": "text"},
      {"name": "schedule_date", "type": "date"},
      {"name": "status", "type": "text"},
      {"name": "total_amount", "type": "numeric"},
      {"name": "items", "type": "jsonb"},
      {"name": "client_name", "type": "text"},
      {"name": "client_tin", "type": "text"},
      {"name": "taxcore_invoice_number", "type": "text"},
      {"name": "taxcore_submitted_at", "type": "timestamptz"},
      {"name": "org_id", "type": "uuid"},
      {"name": "isDeleted", "type": "boolean"},
      {"name": "updatedAt", "type": "timestamptz"}
    ]
  }'::jsonb,
  FALSE,
  now()
WHERE NOT EXISTS (
  SELECT 1 FROM tenant_tables WHERE table_name = 'cost_schedules'
);
