-- Phase 15: Quotations module (phase 1)
START TRANSACTION;

CREATE TABLE IF NOT EXISTS quotations (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  quotation_no VARCHAR(60) NOT NULL,
  quotation_date DATE NOT NULL,
  valid_until DATE NULL,
  party_id BIGINT UNSIGNED NOT NULL,
  status ENUM('draft','sent','approved','rejected','expired','converted') NOT NULL DEFAULT 'draft',
  subtotal DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  discount_amount DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  tax_amount DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  total_amount DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  notes TEXT NULL,
  terms_conditions TEXT NULL,
  created_by BIGINT UNSIGNED NULL,
  sent_at DATETIME NULL,
  converted_at DATETIME NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_quotation_no (quotation_no),
  KEY idx_quotations_party_status_valid (party_id, status, valid_until),
  KEY idx_quotations_date (quotation_date),
  CONSTRAINT fk_quotations_party FOREIGN KEY (party_id) REFERENCES parties(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS quotation_items (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  quotation_id BIGINT UNSIGNED NOT NULL,
  item_id BIGINT UNSIGNED NOT NULL,
  unit_id BIGINT UNSIGNED NULL,
  qty DECIMAL(18,3) NOT NULL DEFAULT 1.000,
  unit_price DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  discount_type ENUM('none','percent','amount') NOT NULL DEFAULT 'none',
  discount_value DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  line_total DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  notes VARCHAR(255) NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_qi_quotation (quotation_id),
  KEY idx_qi_item (item_id),
  CONSTRAINT fk_qi_quotation FOREIGN KEY (quotation_id) REFERENCES quotations(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS quotation_dispatch_logs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  quotation_id BIGINT UNSIGNED NOT NULL,
  channel ENUM('email','whatsapp') NOT NULL,
  recipient VARCHAR(190) NOT NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'pending',
  message TEXT NULL,
  sent_by BIGINT UNSIGNED NULL,
  sent_at DATETIME NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_qdl_quotation (quotation_id),
  CONSTRAINT fk_qdl_quotation FOREIGN KEY (quotation_id) REFERENCES quotations(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT IGNORE INTO permissions (code, name, module) VALUES
('quotations.view', 'عرض عروض الأسعار', 'quotations'),
('quotations.create', 'إنشاء/تعديل عروض الأسعار', 'quotations'),
('quotations.print', 'طباعة عروض الأسعار', 'quotations'),
('quotations.send', 'إرسال عروض الأسعار', 'quotations'),
('quotations.convert', 'تحويل عرض السعر', 'quotations');

INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT 1, p.id FROM permissions p
WHERE p.code IN ('quotations.view','quotations.create','quotations.print','quotations.send','quotations.convert');

COMMIT;