-- Company Forms: DB-template-driven forms system.
-- One flexible schema renders ANY form from template rows; adding a new form = seed SQL only.
-- Templates are company-INDEPENDENT (every company sees every active template);
-- entries (company_form) belong to a company. First seed: Rancho Mesa "Automobile Accident Report" (EN/ES).
--
-- Idempotent AND PostgreSQL 9.4-compatible: CREATE TABLE IF NOT EXISTS, DO-block index/
-- column guards (no IF NOT EXISTS on indexes - 9.5+), and WHERE NOT EXISTS dedupe on the
-- deterministic-id seeds (no ON CONFLICT - 9.5+). Re-running is safe.
--
-- Apply manually: psql -d <local dev db> -f sql/migrations/2026_07_11_company_forms.sql

-- ============================== Template side ==============================

CREATE TABLE IF NOT EXISTS company_form_template (
  id              varchar(32) PRIMARY KEY,
  code            varchar(40) NOT NULL UNIQUE,       -- machine name, e.g. 'auto_accident'
  title_en        text NOT NULL,
  title_es        text,
  instructions_en text,                              -- newline-separated: first line = heading, rest = ordered steps
  instructions_es text,
  recipients      text,                              -- comma-separated emails notified on submit
  status          varchar(20) NOT NULL DEFAULT 'active',  -- active | inactive
  created         timestamp NOT NULL DEFAULT NOW(),
  created_by      varchar(32)
);

CREATE TABLE IF NOT EXISTS company_form_template_section (
  id           varchar(32) PRIMARY KEY,
  template_id  varchar(32) NOT NULL,
  title_en     text,                                 -- NULL/'' = untitled block (e.g. legal notice)
  title_es     text,
  sort         integer NOT NULL DEFAULT 0,
  is_repeating varchar(1) NOT NULL DEFAULT '0',      -- '1' = repeating group (sub-records)
  max_rows     integer NOT NULL DEFAULT 0            -- repeating only; 0 = unlimited
);
DO $$ BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_class WHERE relname = 'cfts_tpl_idx') THEN
    CREATE INDEX cfts_tpl_idx ON company_form_template_section (template_id, sort);
  END IF;
END $$;

CREATE TABLE IF NOT EXISTS company_form_template_field (
  id            varchar(32) PRIMARY KEY,
  section_id    varchar(32) NOT NULL,
  field_key     varchar(60) NOT NULL,                -- stable snake_case key
  label_en      text NOT NULL,
  label_es      text,
  data_type     varchar(20) NOT NULL DEFAULT 'text', -- text|textarea|date|time|phone|yesno|note
  required      varchar(1) NOT NULL DEFAULT '0',
  sort          integer NOT NULL DEFAULT 0,
  width         integer NOT NULL DEFAULT 12,         -- 12-col grid span (6|3|3 = MAKE|MODEL|YEAR row)
  default_value text                                 -- prefilled into new drafts
);
DO $$ BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_class WHERE relname = 'cftf_sec_idx') THEN
    CREATE INDEX cftf_sec_idx ON company_form_template_field (section_id, sort);
  END IF;
END $$;

-- ================================ Entry side ================================

CREATE TABLE IF NOT EXISTS company_form (
  id           varchar(32) PRIMARY KEY,
  company      varchar(32) NOT NULL,
  template_id  varchar(32) NOT NULL,
  user_id      varchar(32) NOT NULL,
  status       varchar(20) NOT NULL DEFAULT 'draft', -- draft | submitted | locked
  deleted      varchar(1) NOT NULL DEFAULT '0',      -- soft delete
  created      timestamp NOT NULL DEFAULT NOW(),
  submitted_at timestamp
);
DO $$ BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_class WHERE relname = 'cf_company_idx') THEN
    CREATE INDEX cf_company_idx ON company_form (company, template_id, status, deleted);
  END IF;
END $$;
DO $$ BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_class WHERE relname = 'cf_user_idx') THEN
    CREATE INDEX cf_user_idx ON company_form (user_id, deleted);
  END IF;
END $$;

CREATE TABLE IF NOT EXISTS company_form_fields (
  form_id  varchar(32) NOT NULL,
  field_id varchar(32) NOT NULL,                     -- -> company_form_template_field.id
  value    text,
  PRIMARY KEY (form_id, field_id)
);

CREATE TABLE IF NOT EXISTS company_form_sub_record (
  id         varchar(32) PRIMARY KEY,
  form_id    varchar(32) NOT NULL,
  section_id varchar(32) NOT NULL,                   -- -> repeating template section
  sort       integer NOT NULL DEFAULT 0              -- row order within the group
);
DO $$ BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_class WHERE relname = 'cfsr_form_idx') THEN
    CREATE INDEX cfsr_form_idx ON company_form_sub_record (form_id, section_id, sort);
  END IF;
END $$;

CREATE TABLE IF NOT EXISTS company_form_sub_record_fields (
  sub_record_id varchar(32) NOT NULL,
  field_id      varchar(32) NOT NULL,
  value         text,
  PRIMARY KEY (sub_record_id, field_id)
);

CREATE TABLE IF NOT EXISTS company_form_notification (
  id      varchar(32) PRIMARY KEY,
  form_id varchar(32) NOT NULL,
  sent_to varchar(200) NOT NULL,
  sent_at timestamp NOT NULL DEFAULT NOW(),
  subject text
);
DO $$ BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_class WHERE relname = 'cfn_form_idx') THEN
    CREATE INDEX cfn_form_idx ON company_form_notification (form_id, sent_at);
  END IF;
END $$;

-- ====================== Seed: Automobile Accident Report ======================
-- One global template. Deterministic ids:
--   template id = upper(md5('companyform:auto_accident'))
--   section  id = upper(md5(template.id || ':' || section_key))
--   field    id = upper(md5(section.id  || ':' || field_key))

INSERT INTO company_form_template
  (id, code, title_en, title_es, instructions_en, instructions_es, recipients, status)
SELECT
  upper(md5('companyform:auto_accident')), 'auto_accident',
  'Automobile Accident Report',
  'Informe de Accidente Automovilístico',
  E'DO NOT ADMIT FAULT\nStop immediately and pull to a safe location. Then, notify the police.\nDetermine if there are injuries and call for help, if needed.\nTake pictures of the accident scene.\nExchange information with the other driver(s) and witness(es).\nDo not make a statement of any kind or discuss the accident with anyone other than the police officer or your employer.\nReturn this completed form to your manager/employer.',
  E'NO ADMITIR LA FALLA\nPare inmediatamente y vaya a un lugar seguro. Luego, notifique a la policía.\nDetermine si hay lesiones y pida ayuda, si es necesario.\nTome fotos de la escena del accidente.\nIntercambie información con el otro conductor(es) y testigo(s).\nNo haga ninguna declaración de ningún tipo ni discuta el accidente con nadie que no sea el oficial de policía o su empleador.\nDevuelva este formulario completo a su gerente/empleador.',
  'eric@ericj.org',   -- placeholder recipients; UPDATE with the real list
  'active'
WHERE NOT EXISTS (SELECT 1 FROM company_form_template
                  WHERE id = upper(md5('companyform:auto_accident')));

INSERT INTO company_form_template_section (id, template_id, title_en, title_es, sort, is_repeating, max_rows)
SELECT upper(md5(t.id || ':' || s.skey)), t.id, s.title_en, s.title_es, s.sort, s.rep, s.maxr
FROM company_form_template t
CROSS JOIN (VALUES
  ('policy',   'Policy Holder Information',       'Información del Titular de la Póliza', 1, '0', 0),
  ('accident', 'Information About the Accident',  'Información Sobre el Accidente',       2, '0', 0),
  ('vehicle',  'Your Vehicle Information',        'La Información de Su Vehículo',        3, '0', 0),
  ('other',    'Other Vehicle Information',       'Otra Información del Vehículo',        4, '0', 0),
  ('injured',  'Injured Persons',                 'Personas Heridas',                     5, '1', 4),
  ('witness',  'Witnesses',                       'Testigos',                             6, '1', 3),
  ('legal',    NULL,                              NULL,                                   7, '0', 0)
) AS s(skey, title_en, title_es, sort, rep, maxr)
WHERE t.code = 'auto_accident'
  AND NOT EXISTS (SELECT 1 FROM company_form_template_section es
                  WHERE es.id = upper(md5(t.id || ':' || s.skey)));

INSERT INTO company_form_template_field
  (id, section_id, field_key, label_en, label_es, data_type, required, sort, width, default_value)
SELECT upper(md5(sec.id || ':' || f.fkey)), sec.id, f.fkey,
       f.label_en, f.label_es, f.dtype, f.req, f.sort, f.width, f.dflt
FROM company_form_template t
JOIN (VALUES
  -- skey, fkey, label_en, label_es, dtype, req, sort, width, default
  ('policy', 'policy_name',    'Name',            'Nombre',              'text', '1', 1,  6, 'LUSA Holdings LLC'),
  ('policy', 'policy_carrier', 'Insurance Carrier','Aseguradora',        'text', '1', 2,  6, 'Middlesex Insurance Co.'),
  ('policy', 'policy_number',  'Policy Number',   'Número de Póliza',    'text', '1', 3,  6, 'A0197157001'),
  ('policy', 'policy_contact', 'Contact Person',  'Persona de Contacto', 'text', '0', 4,  6, NULL),

  ('accident', 'acc_date',          'Date of Accident',       'Fecha del Accidente',      'date',     '1', 1,  6, NULL),
  ('accident', 'acc_time',          'Time',                   'Hora',                     'time',     '1', 2,  6, NULL),
  ('accident', 'acc_location',      'Location of Accident',   'Ubicación del Accidente',  'text',     '1', 3, 12, NULL),
  ('accident', 'acc_police_report', 'Police Report',          'Reporte Policial',         'yesno',    '0', 4, 12, NULL),
  ('accident', 'acc_description',   'Description of Accident','Descripción del Accidente','textarea', '1', 5, 12, NULL),

  ('vehicle', 'veh_driver',        'Name of Driver',      'Nombre del Conductor',   'text',     '1', 1, 12, NULL),
  ('vehicle', 'veh_license',       'Driver''s License #', 'Licencia de Conducir #', 'text',     '0', 2,  8, NULL),
  ('vehicle', 'veh_license_state', 'State',               'Estado',                 'text',     '0', 3,  4, NULL),
  ('vehicle', 'veh_make',          'Vehicle Make',        'Marca del Vehículo',     'text',     '0', 4,  6, NULL),
  ('vehicle', 'veh_model',         'Model',               'Modelo',                 'text',     '0', 5,  3, NULL),
  ('vehicle', 'veh_year',          'Year',                'Año',                    'text',     '0', 6,  3, NULL),
  ('vehicle', 'veh_vin',           'VIN #',               'VIN #',                  'text',     '0', 7, 12, NULL),
  ('vehicle', 'veh_damage',        'Description of Damage to Vehicle', 'Descripción del Daño al Vehículo', 'textarea', '0', 8, 12, NULL),

  ('other', 'other_driver',        'Name of Driver',      'Nombre del Conductor',       'text',     '0', 1,  6, NULL),
  ('other', 'other_phone',         'Daytime Phone',       'Teléfono Durante el Día',    'phone',    '0', 2,  6, NULL),
  ('other', 'other_license',       'Driver''s License #', 'Licencia de Conducir #',     'text',     '0', 3,  8, NULL),
  ('other', 'other_license_state', 'State',               'Estado',                     'text',     '0', 4,  4, NULL),
  ('other', 'other_make',          'Vehicle Make',        'Marca del Vehículo',         'text',     '0', 5,  6, NULL),
  ('other', 'other_model',         'Model',               'Modelo',                     'text',     '0', 6,  3, NULL),
  ('other', 'other_year',          'Year',                'Año',                        'text',     '0', 7,  3, NULL),
  ('other', 'other_plate',         'License Plate',       'Placa',                      'text',     '0', 8, 12, NULL),
  ('other', 'other_carrier',       'Insurance Carrier',   'Aseguradora',                'text',     '0', 9,  6, NULL),
  ('other', 'other_policy',        'Policy Number',       'Número de Póliza',           'text',     '0', 10, 6, NULL),
  ('other', 'other_damage',        'Description of Damage to Vehicle', 'Descripción del Daño al Vehículo', 'textarea', '0', 11, 12, NULL),

  ('injured', 'inj_name',   'Name',             'Nombre',                  'text',     '0', 1,  6, NULL),
  ('injured', 'inj_phone',  'Daytime Phone',    'Teléfono Durante el Día', 'phone',    '0', 2,  6, NULL),
  ('injured', 'inj_extent', 'Extent of Injury', 'Alcance de la Lesión',    'textarea', '0', 3, 12, NULL),

  ('witness', 'wit_name',    'Name',          'Nombre',                  'text',  '0', 1,  6, NULL),
  ('witness', 'wit_phone',   'Daytime Phone', 'Teléfono Durante el Día', 'phone', '0', 2,  6, NULL),
  ('witness', 'wit_address', 'Address',       'Dirección',               'text',  '0', 3, 12, NULL),

  ('legal', 'legal_notice',
   'California law requires traffic accidents on a California street/highway or private property to be reported to the Department of Motor Vehicles (DMV) within 10 days if there was an injury, death or property damage in excess of $1,000. Untimely reporting could result in DMV suspending a driver license. Accidents involving vehicles not required to be registered such as an off-road vehicle (OHV), implement of husbandry, or snowmobile or occurring on a military base or occurring on the driver''s own property involving only the personal property of the driver and there was no injury or death are not reportable. The law requires the driver to file the SR 1 form with the DMV regardless of fault. The report must be made in addition to any other report filed with a law enforcement agency, insurance company, or the California Highway Patrol (CHP) as their reports do not satisfy the filing requirement.',
   'La ley de California exige que los accidentes de tráfico en una calle o carretera de California o en una propiedad privada se notifiquen al Departamento de Vehículos Motorizados (DMV) en un plazo de 10 días si hubo una lesión, muerte o daños a la propiedad que superen los $1,000. Informes inoportunos podrían resultar en que el DMV suspenda una licencia de conducir. Accidentes que involucren vehículos no requeridos para ser registrados tales como un vehículo todoterreno (OHV), implemento de cría, o moto de nieve o que ocurra en una base militar o que ocurran en la propiedad del conductor que involucre solo la propiedad personal del conductor y no haya lesión o muerte no son reportables. La ley exige que el conductor presente el formulario SR 1 con el DMV independientemente de la culpa. El informe debe hacerse además de cualquier otro informe presentado ante una agencia de aplicación de la ley, compañía de seguros o la Patrulla de Caminos de California (CHP) ya que sus informes no satisfacen los requisitos de presentación.',
   'note', '0', 1, 12, NULL)
) AS f(skey, fkey, label_en, label_es, dtype, req, sort, width, dflt) ON true
JOIN company_form_template_section sec
  ON sec.id = upper(md5(t.id || ':' || f.skey))
WHERE t.code = 'auto_accident'
  AND NOT EXISTS (SELECT 1 FROM company_form_template_field ef
                  WHERE ef.id = upper(md5(sec.id || ':' || f.fkey)));

-- ====================== users: company-form manager flag ======================
-- '1' = user can administer company forms (admin panel, lock/unlock, etc.).
-- Granted per PERSON via bind_id fan-out in /admin/companyFormMgrs.
DO $$ BEGIN
  IF NOT EXISTS (SELECT 1 FROM information_schema.columns
                 WHERE table_name = 'users' AND column_name = 'is_company_form_mgr') THEN
    ALTER TABLE users ADD COLUMN is_company_form_mgr varchar(1) DEFAULT '0';
  END IF;
END $$;
