-- Asset module Phase 1.1: AMC / Service Visits / Insurance child tables
-- Run once: psql "$DATABASE_URL" -f scripts/patch-assets-amc-insurance.sql

-- Remove inline AMC/insurance from assets
ALTER TABLE assets
  DROP COLUMN IF EXISTS amc_start_date,
  DROP COLUMN IF EXISTS amc_end_date,
  DROP COLUMN IF EXISTS amc_vendor_id,
  DROP COLUMN IF EXISTS insurance_policy_no,
  DROP COLUMN IF EXISTS insurance_expiry_date;

-- AMC contracts
CREATE TABLE IF NOT EXISTS asset_amc_contracts (
  id                  BIGSERIAL     PRIMARY KEY,
  asset_id            BIGINT        NOT NULL REFERENCES assets(id) ON DELETE CASCADE,
  vendor_id           BIGINT        NOT NULL REFERENCES vendors(id) ON DELETE RESTRICT,
  contract_no         VARCHAR(100),
  contract_type       VARCHAR(30)   NOT NULL DEFAULT 'COMPREHENSIVE'
                        CHECK (contract_type IN ('COMPREHENSIVE','LABOUR_ONLY','PARTS_ONLY','PREVENTIVE_ONLY')),
  start_date          DATE          NOT NULL,
  end_date            DATE          NOT NULL,
  renewal_date        DATE,
  annual_cost         NUMERIC(12,2) NOT NULL DEFAULT 0,
  payment_frequency   VARCHAR(20)   NOT NULL DEFAULT 'ANNUAL'
                        CHECK (payment_frequency IN ('MONTHLY','QUARTERLY','HALF_YEARLY','ANNUAL')),
  service_frequency   VARCHAR(20)
                        CHECK (service_frequency IN ('MONTHLY','QUARTERLY','HALF_YEARLY','ANNUAL')),
  visits_per_year     INT,
  contact_person      VARCHAR(150),
  contact_phone       VARCHAR(15),
  contact_email       VARCHAR(150),
  scope_of_work       TEXT,
  exclusions          TEXT,
  remarks             TEXT,
  is_active           BOOLEAN       NOT NULL DEFAULT TRUE,
  created_by          BIGINT        REFERENCES users(id) ON DELETE SET NULL,
  updated_by          BIGINT        REFERENCES users(id) ON DELETE SET NULL,
  created_at          TIMESTAMPTZ   NOT NULL DEFAULT NOW(),
  updated_at          TIMESTAMPTZ   NOT NULL DEFAULT NOW(),
  deleted_at          TIMESTAMPTZ
);

CREATE INDEX IF NOT EXISTS idx_amc_asset_id  ON asset_amc_contracts(asset_id);
CREATE INDEX IF NOT EXISTS idx_amc_vendor_id ON asset_amc_contracts(vendor_id);
CREATE INDEX IF NOT EXISTS idx_amc_end_date  ON asset_amc_contracts(end_date) WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_amc_is_active ON asset_amc_contracts(asset_id, is_active);

DROP TRIGGER IF EXISTS trg_asset_amc_contracts_updated_at ON asset_amc_contracts;
CREATE TRIGGER trg_asset_amc_contracts_updated_at
  BEFORE UPDATE ON asset_amc_contracts
  FOR EACH ROW EXECUTE FUNCTION fn_set_updated_at();

-- Service visits
CREATE TABLE IF NOT EXISTS asset_service_visits (
  id                    BIGSERIAL     PRIMARY KEY,
  asset_id              BIGINT        NOT NULL REFERENCES assets(id) ON DELETE CASCADE,
  amc_contract_id       BIGINT        REFERENCES asset_amc_contracts(id) ON DELETE SET NULL,
  visit_type            VARCHAR(30)   NOT NULL
                          CHECK (visit_type IN ('PREVENTIVE','BREAKDOWN','INSPECTION','INSTALLATION','CALIBRATION','OTHER')),
  visit_date            DATE          NOT NULL,
  visit_number          INT,
  complaint_no          VARCHAR(50),
  complaint_date        DATE,
  complaint_desc        TEXT,
  engineer_name         VARCHAR(150),
  engineer_phone        VARCHAR(15),
  vendor_id             BIGINT        REFERENCES vendors(id) ON DELETE SET NULL,
  work_done             TEXT,
  parts_replaced        TEXT,
  next_service_date     DATE,
  status                VARCHAR(20)   NOT NULL DEFAULT 'COMPLETED'
                          CHECK (status IN ('SCHEDULED','IN_PROGRESS','COMPLETED','CANCELLED','PENDING_PARTS')),
  downtime_hours        NUMERIC(6,2)  DEFAULT 0,
  service_cost          NUMERIC(12,2) DEFAULT 0,
  is_under_amc          BOOLEAN       NOT NULL DEFAULT FALSE,
  asset_condition_after VARCHAR(20)
                          CHECK (asset_condition_after IN ('GOOD','FAIR','POOR','NEEDS_REPLACEMENT')),
  remarks               TEXT,
  created_by            BIGINT        REFERENCES users(id) ON DELETE SET NULL,
  updated_by            BIGINT        REFERENCES users(id) ON DELETE SET NULL,
  created_at            TIMESTAMPTZ   NOT NULL DEFAULT NOW(),
  updated_at            TIMESTAMPTZ   NOT NULL DEFAULT NOW(),
  deleted_at            TIMESTAMPTZ
);

CREATE INDEX IF NOT EXISTS idx_service_asset_id   ON asset_service_visits(asset_id);
CREATE INDEX IF NOT EXISTS idx_service_amc_id     ON asset_service_visits(amc_contract_id);
CREATE INDEX IF NOT EXISTS idx_service_visit_date ON asset_service_visits(visit_date);
CREATE INDEX IF NOT EXISTS idx_service_status     ON asset_service_visits(status) WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_service_next_date  ON asset_service_visits(next_service_date) WHERE deleted_at IS NULL;

DROP TRIGGER IF EXISTS trg_asset_service_visits_updated_at ON asset_service_visits;
CREATE TRIGGER trg_asset_service_visits_updated_at
  BEFORE UPDATE ON asset_service_visits
  FOR EACH ROW EXECUTE FUNCTION fn_set_updated_at();

-- Insurance policies
CREATE TABLE IF NOT EXISTS asset_insurance_policies (
  id                  BIGSERIAL     PRIMARY KEY,
  asset_id            BIGINT        NOT NULL REFERENCES assets(id) ON DELETE CASCADE,
  policy_no           VARCHAR(100)  NOT NULL,
  insurer_name        VARCHAR(200)  NOT NULL,
  insurer_branch      VARCHAR(150),
  insurer_contact     VARCHAR(150),
  insurer_phone       VARCHAR(15),
  insurer_email       VARCHAR(150),
  policy_type         VARCHAR(50)   NOT NULL DEFAULT 'FIRE_AND_ALLIED'
                        CHECK (policy_type IN ('FIRE_AND_ALLIED','MACHINERY_BREAKDOWN','COMPREHENSIVE','THIRD_PARTY','VEHICLE','OTHER')),
  sum_insured         NUMERIC(15,2) NOT NULL DEFAULT 0,
  annual_premium      NUMERIC(12,2) NOT NULL DEFAULT 0,
  policy_start_date   DATE          NOT NULL,
  policy_end_date     DATE          NOT NULL,
  renewal_date        DATE,
  is_auto_renewal     BOOLEAN       NOT NULL DEFAULT FALSE,
  premium_paid        BOOLEAN       NOT NULL DEFAULT FALSE,
  premium_paid_date   DATE,
  remarks             TEXT,
  is_active           BOOLEAN       NOT NULL DEFAULT TRUE,
  created_by          BIGINT        REFERENCES users(id) ON DELETE SET NULL,
  updated_by          BIGINT        REFERENCES users(id) ON DELETE SET NULL,
  created_at          TIMESTAMPTZ   NOT NULL DEFAULT NOW(),
  updated_at          TIMESTAMPTZ   NOT NULL DEFAULT NOW(),
  deleted_at          TIMESTAMPTZ
);

CREATE INDEX IF NOT EXISTS idx_insurance_asset_id  ON asset_insurance_policies(asset_id);
CREATE INDEX IF NOT EXISTS idx_insurance_end_date  ON asset_insurance_policies(policy_end_date) WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_insurance_is_active ON asset_insurance_policies(asset_id, is_active);

DROP TRIGGER IF EXISTS trg_asset_insurance_policies_updated_at ON asset_insurance_policies;
CREATE TRIGGER trg_asset_insurance_policies_updated_at
  BEFORE UPDATE ON asset_insurance_policies
  FOR EACH ROW EXECUTE FUNCTION fn_set_updated_at();

-- Extend asset_attachments
ALTER TABLE asset_attachments
  ADD COLUMN IF NOT EXISTS amc_contract_id  BIGINT REFERENCES asset_amc_contracts(id) ON DELETE CASCADE,
  ADD COLUMN IF NOT EXISTS service_visit_id BIGINT REFERENCES asset_service_visits(id) ON DELETE CASCADE,
  ADD COLUMN IF NOT EXISTS insurance_id     BIGINT REFERENCES asset_insurance_policies(id) ON DELETE CASCADE;

ALTER TABLE asset_attachments DROP CONSTRAINT IF EXISTS asset_attachments_attachment_type_check;
ALTER TABLE asset_attachments
  ADD CONSTRAINT asset_attachments_attachment_type_check
    CHECK (attachment_type IN (
      'DOCUMENT','PHOTO','WARRANTY','INVOICE',
      'AMC_CONTRACT','SERVICE_REPORT','INSURANCE_POLICY','OTHER'
    ));

-- Views: run scripts/patch-assets-views.sql after this file if not using \i