-- Shark CRM update: quotations + permission aliases cleanup
-- Run this file only. No old item_questions table is needed anymore.

DROP TABLE IF EXISTS item_questions;
DROP TABLE IF EXISTS lead_question_answers;

CREATE TABLE IF NOT EXISTS quotations (
  id INT NOT NULL AUTO_INCREMENT,
  account_id INT NOT NULL,
  customer_id INT NOT NULL,
  item_id INT NOT NULL,
  quantity INT NOT NULL DEFAULT 1,
  unit_price DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  discount DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  tva_percent DECIMAL(8,2) NOT NULL DEFAULT 0.00,
  tva_value DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  total DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  status ENUM('draft','sent','accepted','rejected') NOT NULL DEFAULT 'draft',
  valid_until DATE DEFAULT NULL,
  notes TEXT DEFAULT NULL,
  created_by INT DEFAULT NULL,
  updated_by INT DEFAULT NULL,
  date_created DATETIME DEFAULT CURRENT_TIMESTAMP,
  date_updated DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_quotations_account (account_id),
  KEY idx_quotations_customer (customer_id),
  KEY idx_quotations_item (item_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Give current admins / owners quotation permissions if they already have full account access.
INSERT IGNORE INTO user_permissions (account_id, user_id, page_key, action_key, allowed)
SELECT u.account_id, u.id, p.page_key, p.action_key, 1
FROM users u
JOIN (
  SELECT 'quotations' page_key, 'view' action_key UNION ALL
  SELECT 'quotations', 'add' UNION ALL
  SELECT 'quotations', 'edit' UNION ALL
  SELECT 'quotations', 'delete' UNION ALL
  SELECT 'quotations', 'open' UNION ALL
  SELECT 'quotations', 'print'
) p
WHERE u.is_super_admin=1 OR u.is_account_owner=1 OR u.role_id=1;
