-- FIAC Final Inspection + Billing (run after bookings_schema.sql)

USE fiac_db;

ALTER TABLE service_bookings
  MODIFY status ENUM(
    'pending',
    'accepted',
    'inspection_done',
    'estimate_sent',
    'approved',
    'assigned',
    'in_progress',
    'completed',
    'final_inspection_done',
    'billed',
    'cancelled'
  ) NOT NULL DEFAULT 'pending';

CREATE TABLE IF NOT EXISTS booking_final_inspections (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  booking_id INT UNSIGNED NOT NULL,
  audio_url VARCHAR(500) NOT NULL,
  text_remarks TEXT DEFAULT NULL,
  inspected_by INT UNSIGNED NOT NULL,
  inspected_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_final_inspection_booking (booking_id),
  CONSTRAINT fk_final_inspection_booking FOREIGN KEY (booking_id) REFERENCES service_bookings (id) ON DELETE CASCADE,
  CONSTRAINT fk_final_inspection_by FOREIGN KEY (inspected_by) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS invoices (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  invoice_number VARCHAR(30) NOT NULL,
  booking_id INT UNSIGNED NOT NULL,
  estimate_id INT UNSIGNED DEFAULT NULL,
  subtotal DECIMAL(12, 2) NOT NULL DEFAULT 0,
  tax_amount DECIMAL(12, 2) NOT NULL DEFAULT 0,
  total_amount DECIMAL(12, 2) NOT NULL DEFAULT 0,
  paid_amount DECIMAL(12, 2) NOT NULL DEFAULT 0,
  balance_amount DECIMAL(12, 2) NOT NULL DEFAULT 0,
  payment_status ENUM('unpaid', 'partial', 'paid') NOT NULL DEFAULT 'unpaid',
  pdf_path VARCHAR(500) DEFAULT NULL,
  notes TEXT DEFAULT NULL,
  generated_by INT UNSIGNED NOT NULL,
  generated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_invoice_number (invoice_number),
  UNIQUE KEY uq_invoice_booking (booking_id),
  CONSTRAINT fk_invoice_booking FOREIGN KEY (booking_id) REFERENCES service_bookings (id),
  CONSTRAINT fk_invoice_estimate FOREIGN KEY (estimate_id) REFERENCES booking_estimates (id) ON DELETE SET NULL,
  CONSTRAINT fk_invoice_generated_by FOREIGN KEY (generated_by) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS invoice_lines (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  invoice_id INT UNSIGNED NOT NULL,
  item_type ENUM('labor', 'part') NOT NULL DEFAULT 'labor',
  description VARCHAR(255) NOT NULL,
  quantity DECIMAL(10, 2) NOT NULL DEFAULT 1,
  unit_price DECIMAL(12, 2) NOT NULL DEFAULT 0,
  line_total DECIMAL(12, 2) NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  KEY idx_invoice_lines_invoice (invoice_id),
  CONSTRAINT fk_invoice_lines_invoice FOREIGN KEY (invoice_id) REFERENCES invoices (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS invoice_payments (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  invoice_id INT UNSIGNED NOT NULL,
  amount DECIMAL(12, 2) NOT NULL,
  payment_method ENUM('cash', 'upi') NOT NULL,
  upi_reference VARCHAR(100) DEFAULT NULL,
  notes VARCHAR(255) DEFAULT NULL,
  recorded_by INT UNSIGNED NOT NULL,
  paid_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_payments_invoice (invoice_id),
  CONSTRAINT fk_payments_invoice FOREIGN KEY (invoice_id) REFERENCES invoices (id) ON DELETE CASCADE,
  CONSTRAINT fk_payments_recorded_by FOREIGN KEY (recorded_by) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;