-- Single installation SQL for the whole system
CREATE DATABASE IF NOT EXISTS mms CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE mms;

CREATE TABLE system_settings (
  id INT AUTO_INCREMENT PRIMARY KEY,
  default_language VARCHAR(10) DEFAULT 'en',
  languages_enabled TINYINT(1) DEFAULT 1,
  default_currency VARCHAR(10) DEFAULT 'BHD',
  currency_symbol VARCHAR(5) DEFAULT 'BD',
  organization_name VARCHAR(255) DEFAULT NULL,
  organization_logo VARCHAR(255) DEFAULT NULL,
  organization_address TEXT DEFAULT NULL,
  organization_phone VARCHAR(50) DEFAULT NULL,
  organization_email VARCHAR(100) DEFAULT NULL,
  allow_tokenization TINYINT(1) DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO system_settings (default_language, languages_enabled, default_currency, currency_symbol) VALUES ('en',1,'BHD','BD');

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(100) UNIQUE,
  email VARCHAR(255) UNIQUE,
  password_hash VARCHAR(255),
  role ENUM('superadmin','admin','member') DEFAULT 'member',
  membership_type_id INT DEFAULT NULL,
  subscription_active TINYINT(1) DEFAULT 0,
  next_due_date DATE DEFAULT NULL,
  payment_gateway VARCHAR(50) DEFAULT NULL,
  payment_token VARCHAR(255) DEFAULT NULL,
  auto_renew_enabled TINYINT(1) DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE membership_types (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name JSON NOT NULL,
  fee_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  billing_cycle ENUM('monthly','quarterly','yearly') DEFAULT 'yearly',
  auto_renew TINYINT(1) DEFAULT 1,
  active TINYINT(1) DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE translations (
  id INT AUTO_INCREMENT PRIMARY KEY,
  lang VARCHAR(10) NOT NULL,
  word_key VARCHAR(100) NOT NULL,
  translation TEXT NOT NULL,
  UNIQUE(lang, word_key)
);

CREATE TABLE custom_fields (
  id INT AUTO_INCREMENT PRIMARY KEY,
  field_label JSON NOT NULL,
  field_name VARCHAR(100) NOT NULL UNIQUE,
  field_type ENUM('text','textarea','email','phone','number','dropdown','checkbox','radio','date') NOT NULL,
  options JSON DEFAULT NULL,
  required TINYINT(1) DEFAULT 0,
  sort_order INT DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE member_field_values (
  id INT AUTO_INCREMENT PRIMARY KEY,
  member_id INT NOT NULL,
  field_id INT NOT NULL,
  field_value TEXT,
  INDEX(member_id),
  FOREIGN KEY (member_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (field_id) REFERENCES custom_fields(id) ON DELETE CASCADE
);

CREATE TABLE invoices (
  id INT AUTO_INCREMENT PRIMARY KEY,
  member_id INT NOT NULL,
  membership_type_id INT NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  currency VARCHAR(10) NOT NULL,
  status ENUM('unpaid','paid','cancelled') DEFAULT 'unpaid',
  issued_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  due_date DATE DEFAULT NULL,
  paid_at TIMESTAMP NULL,
  payment_method VARCHAR(50) DEFAULT NULL,
  transaction_id VARCHAR(100) DEFAULT NULL,
  original_currency VARCHAR(10) DEFAULT NULL,
  original_symbol VARCHAR(5) DEFAULT NULL,
  INDEX(member_id),
  FOREIGN KEY (member_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE dashboard_widgets (
  id INT AUTO_INCREMENT PRIMARY KEY,
  widget_key VARCHAR(100) NOT NULL,
  title JSON DEFAULT NULL,
  description JSON DEFAULT NULL,
  icon VARCHAR(100) DEFAULT NULL,
  active TINYINT(1) DEFAULT 1
);

CREATE TABLE dashboard_visibility (
  id INT AUTO_INCREMENT PRIMARY KEY,
  widget_id INT NOT NULL,
  role ENUM('superadmin','admin','member') NOT NULL,
  membership_type_id INT DEFAULT NULL,
  visible TINYINT(1) DEFAULT 1,
  pos_x INT DEFAULT 0,
  pos_y INT DEFAULT 0,
  width INT DEFAULT 2,
  height INT DEFAULT 1,
  FOREIGN KEY (widget_id) REFERENCES dashboard_widgets(id)
);

CREATE TABLE permissions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  role ENUM('admin','member') NOT NULL,
  element VARCHAR(100) NOT NULL,
  allowed TINYINT(1) DEFAULT 0
);

CREATE TABLE payment_gateways (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  active TINYINT(1) DEFAULT 0,
  credentials JSON DEFAULT NULL
);

CREATE TABLE announcements (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  content TEXT NOT NULL,
  target_type ENUM('all','member_type','individual') DEFAULT 'all',
  target_id INT NULL,
  created_by INT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  auto_generated BOOLEAN DEFAULT 0,
  scheduled_at TIMESTAMP NULL,
  status ENUM('pending','sent','cancelled') DEFAULT 'pending',
  recurrence ENUM('none','daily','weekly','monthly','yearly') DEFAULT 'none',
  recurrence_end DATE NULL
);

CREATE TABLE notification_rules (
  id INT AUTO_INCREMENT PRIMARY KEY,
  rule_name VARCHAR(255) NOT NULL,
  condition_type ENUM('late_payment','upcoming_expiry') NOT NULL,
  days_before_or_after INT DEFAULT 0,
  message_template TEXT NOT NULL,
  active BOOLEAN DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE announcement_logs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  announcement_id INT NOT NULL,
  member_id INT NOT NULL,
  delivered BOOLEAN DEFAULT 0,
  delivered_at TIMESTAMP NULL
);

-- seed admin user (password ChangeMe123!)
INSERT INTO users (username, email, password_hash, role) VALUES
('super','admin@example.com', '$2y$10$u1qRj0PZ6G7a7b1wR3sJkuW3Zqk1jF/3vE4ZzQmYxv8o9a0KJp6G', 'superadmin');

INSERT INTO dashboard_widgets (widget_key, title, description, icon) VALUES
('total_members', JSON_OBJECT('en','Total Members','ar','إجمالي الأعضاء'), NULL, 'users'),
('income_month', JSON_OBJECT('en','Income This Month','ar','دخل هذا الشهر'), NULL, 'cash');