-- Run this in phpMyAdmin after selecting scitglo1_smart_order_management.
-- Safe to run once. It does not drop or replace existing tables.
CREATE TABLE IF NOT EXISTS subscription_plans (
  plan_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  plan_name VARCHAR(80) NOT NULL UNIQUE,
  duration_months INT UNSIGNED NOT NULL,
  price DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  updated_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS user_subscriptions (
  subscription_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NOT NULL,
  plan_id INT UNSIGNED NULL,
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,
  price DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  status ENUM('Active','Expired','Cancelled') NOT NULL DEFAULT 'Active',
  notes VARCHAR(500) NULL,
  created_by_user_id INT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  INDEX idx_sub_user_end (user_id, end_date),
  CONSTRAINT fk_sub_user FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
  CONSTRAINT fk_sub_plan FOREIGN KEY (plan_id) REFERENCES subscription_plans(plan_id) ON DELETE SET NULL,
  CONSTRAINT fk_sub_created_by FOREIGN KEY (created_by_user_id) REFERENCES users(user_id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO subscription_plans(plan_name,duration_months,price,is_active) VALUES
('3 Months',3,0.00,1),('6 Months',6,0.00,1),('1 Year',12,0.00,1);
INSERT IGNORE INTO permissions(permission_name) VALUES ('Subscription Management');