USE TESVISCOVENTURES;

CREATE TABLE IF NOT EXISTS gift_card_audit_log (
  id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  gift_card_id BIGINT UNSIGNED NOT NULL,
  admin_id     BIGINT UNSIGNED NULL,
  action       VARCHAR(40) NOT NULL,
  before_json  JSON NULL,
  after_json   JSON NULL,
  notes        VARCHAR(500) NOT NULL DEFAULT '',
  created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_gcal_card (gift_card_id, created_at),
  KEY idx_gcal_admin (admin_id, created_at),
  CONSTRAINT gcal_fk_card FOREIGN KEY (gift_card_id) REFERENCES gift_cards(id) ON DELETE CASCADE,
  CONSTRAINT gcal_fk_admin FOREIGN KEY (admin_id) REFERENCES admin_users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gift_card_import_jobs (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  admin_id      BIGINT UNSIGNED NOT NULL,
  amount        DECIMAL(12,2) NOT NULL,
  expiry_date   DATETIME NULL,
  total_rows    INT UNSIGNED NOT NULL DEFAULT 0,
  created_count INT UNSIGNED NOT NULL DEFAULT 0,
  failed_count  INT UNSIGNED NOT NULL DEFAULT 0,
  status        ENUM('pending','processing','completed','failed') NOT NULL DEFAULT 'pending',
  error_log     TEXT NULL,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  completed_at  DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_gcij_admin (admin_id, created_at),
  CONSTRAINT gcij_fk_admin FOREIGN KEY (admin_id) REFERENCES admin_users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gift_card_notes (
  id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  gift_card_id BIGINT UNSIGNED NOT NULL,
  admin_id     BIGINT UNSIGNED NULL,
  body         TEXT NOT NULL,
  created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_gcn_card (gift_card_id, created_at),
  CONSTRAINT gcn_fk_card FOREIGN KEY (gift_card_id) REFERENCES gift_cards(id) ON DELETE CASCADE,
  CONSTRAINT gcn_fk_admin FOREIGN KEY (admin_id) REFERENCES admin_users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO admin_permissions (slug, label) VALUES
  ('giftcards.view',   'View gift cards'),
  ('giftcards.manage', 'Manage gift cards');

-- Grant to roles
INSERT IGNORE INTO admin_role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM admin_roles r CROSS JOIN admin_permissions p
WHERE r.name IN ('super_admin', 'administrator')
  AND p.slug IN ('giftcards.view', 'giftcards.manage');

INSERT IGNORE INTO admin_role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM admin_roles r CROSS JOIN admin_permissions p
WHERE r.name = 'customer_support' AND p.slug = 'giftcards.view';

INSERT IGNORE INTO admin_role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM admin_roles r CROSS JOIN admin_permissions p
WHERE r.name = 'product_manager' AND p.slug = 'giftcards.view';

INSERT IGNORE INTO admin_role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM admin_roles r CROSS JOIN admin_permissions p
WHERE r.name = 'order_manager' AND p.slug = 'giftcards.view';

-- Settings rows
INSERT IGNORE INTO website_settings (setting_key, setting_value, is_secret) VALUES
  ('giftcards_purchase_enabled',          '0', 0),
  ('giftcards_redeem_enabled',            '1', 0),
  ('giftcards_min_amount',                '5', 0),
  ('giftcards_max_amount',                '500', 0),
  ('giftcards_default_expiry_days',       '365', 0),
  ('giftcards_allow_multiple_per_order',  '0', 0),
  ('giftcards_code_length',               '16', 0),
  ('giftcards_banner_enabled',            '0', 0),
  ('giftcards_banner_title',              'Give the gift of good food', 0),
  ('giftcards_banner_subtitle',           'Digital gift cards, delivered instantly', 0),
  ('giftcards_banner_cta_text',           'Buy a gift card', 0),
  ('giftcards_banner_cta_link',           '/gift-cards', 0),
  ('giftcards_email_from_name',           'Elroi Foods', 0),
  ('giftcards_email_from_address',        'noreply@elroifoods.example', 0);