CREATE DATABASE IF NOT EXISTS superb_academy_payments CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE superb_academy_payments;

CREATE TABLE IF NOT EXISTS guardians (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  full_name VARCHAR(150) NOT NULL,
  phone VARCHAR(30) NULL,
  phone_alt VARCHAR(30) NULL,
  email VARCHAR(190) NULL,
  address VARCHAR(255) NULL,
  relationship VARCHAR(50) NULL,
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_guardians_email (email)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS learners (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  guardian_id BIGINT UNSIGNED NULL,
  learner_code VARCHAR(30) NOT NULL,
  first_name VARCHAR(80) NOT NULL,
  last_name VARCHAR(80) NOT NULL,
  date_of_birth DATE NULL,
  gender ENUM('male','female','other') NULL,
  phone VARCHAR(30) NULL,
  email VARCHAR(190) NULL,
  address VARCHAR(255) NULL,
  status ENUM('active','inactive','suspended') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_learners_code (learner_code),
  UNIQUE KEY uq_learners_email (email),
  KEY idx_learners_guardian_id (guardian_id),
  CONSTRAINT fk_learners_guardians FOREIGN KEY (guardian_id) REFERENCES guardians(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS courses (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(120) NOT NULL,
  description TEXT NULL,
  default_monthly_rate DECIMAL(12,2) NOT NULL DEFAULT 0,
  currency CHAR(3) NOT NULL DEFAULT 'GHS',
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS course_sessions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  course_id BIGINT UNSIGNED NOT NULL,
  session_name VARCHAR(120) NOT NULL,
  start_date DATE NULL,
  end_date DATE NULL,
  monthly_rate DECIMAL(12,2) NOT NULL,
  billing_day TINYINT UNSIGNED NOT NULL DEFAULT 1,
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_course_sessions_course_id (course_id),
  CONSTRAINT fk_course_sessions_courses FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS subscriptions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  learner_id BIGINT UNSIGNED NOT NULL,
  guardian_id BIGINT UNSIGNED NULL,
  course_session_id BIGINT UNSIGNED NOT NULL,
  start_date DATE NOT NULL,
  end_date DATE NULL,
  monthly_rate_override DECIMAL(12,2) NULL,
  status ENUM('active','paused','ended') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_subscriptions_learner_id (learner_id),
  KEY idx_subscriptions_guardian_id (guardian_id),
  KEY idx_subscriptions_course_session_id (course_session_id),
  CONSTRAINT fk_subscriptions_learners FOREIGN KEY (learner_id) REFERENCES learners(id) ON DELETE CASCADE,
  CONSTRAINT fk_subscriptions_guardians FOREIGN KEY (guardian_id) REFERENCES guardians(id) ON DELETE SET NULL,
  CONSTRAINT fk_subscriptions_course_sessions FOREIGN KEY (course_session_id) REFERENCES course_sessions(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS subscription_discounts (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  subscription_id BIGINT UNSIGNED NOT NULL,
  discount_type ENUM('fixed','percent') NOT NULL,
  discount_value DECIMAL(12,2) NOT NULL,
  start_month CHAR(7) NOT NULL,
  end_month CHAR(7) NULL,
  reason VARCHAR(255) NULL,
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_subscription_discounts_subscription_id (subscription_id),
  CONSTRAINT fk_subscription_discounts_subscriptions FOREIGN KEY (subscription_id) REFERENCES subscriptions(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS invoices (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  subscription_id BIGINT UNSIGNED NOT NULL,
  learner_id BIGINT UNSIGNED NOT NULL,
  guardian_id BIGINT UNSIGNED NULL,
  course_session_id BIGINT UNSIGNED NOT NULL,
  billing_month CHAR(7) NOT NULL,
  due_date DATE NOT NULL,
  base_amount DECIMAL(12,2) NOT NULL,
  discount_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  amount_due DECIMAL(12,2) NOT NULL,
  amount_paid DECIMAL(12,2) NOT NULL DEFAULT 0,
  status ENUM('unpaid','partial','paid','void') NOT NULL DEFAULT 'unpaid',
  paystack_access_code VARCHAR(80) NULL,
  paystack_reference VARCHAR(120) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_invoices_subscription_month (subscription_id, billing_month),
  KEY idx_invoices_learner_month (learner_id, billing_month),
  KEY idx_invoices_guardian_month (guardian_id, billing_month),
  CONSTRAINT fk_invoices_subscriptions FOREIGN KEY (subscription_id) REFERENCES subscriptions(id) ON DELETE CASCADE,
  CONSTRAINT fk_invoices_learners FOREIGN KEY (learner_id) REFERENCES learners(id) ON DELETE CASCADE,
  CONSTRAINT fk_invoices_guardians FOREIGN KEY (guardian_id) REFERENCES guardians(id) ON DELETE SET NULL,
  CONSTRAINT fk_invoices_course_sessions FOREIGN KEY (course_session_id) REFERENCES course_sessions(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS payments (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  invoice_id BIGINT UNSIGNED NOT NULL,
  learner_id BIGINT UNSIGNED NOT NULL,
  guardian_id BIGINT UNSIGNED NULL,
  amount DECIMAL(12,2) NOT NULL,
  currency CHAR(3) NOT NULL DEFAULT 'GHS',
  method ENUM('paystack','manual') NOT NULL,
  channel VARCHAR(50) NULL,
  paystack_reference VARCHAR(120) NULL,
  external_id VARCHAR(120) NULL,
  paid_at DATETIME NOT NULL,
  received_by VARCHAR(120) NULL,
  notes VARCHAR(255) NULL,
  raw_payload JSON NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_payments_paystack_reference (paystack_reference),
  KEY idx_payments_invoice_id (invoice_id),
  KEY idx_payments_learner_id (learner_id),
  KEY idx_payments_guardian_id (guardian_id),
  CONSTRAINT fk_payments_invoices FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE,
  CONSTRAINT fk_payments_learners FOREIGN KEY (learner_id) REFERENCES learners(id) ON DELETE CASCADE,
  CONSTRAINT fk_payments_guardians FOREIGN KEY (guardian_id) REFERENCES guardians(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS notification_logs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  invoice_id BIGINT UNSIGNED NULL,
  learner_id BIGINT UNSIGNED NULL,
  guardian_id BIGINT UNSIGNED NULL,
  channel ENUM('sms','email') NOT NULL,
  recipient VARCHAR(190) NOT NULL,
  template_key VARCHAR(80) NULL,
  subject VARCHAR(190) NULL,
  message TEXT NOT NULL,
  provider VARCHAR(80) NULL,
  provider_message_id VARCHAR(120) NULL,
  status ENUM('queued','sent','failed') NOT NULL DEFAULT 'queued',
  error_message VARCHAR(255) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  sent_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_notification_logs_invoice_id (invoice_id),
  KEY idx_notification_logs_learner_id (learner_id),
  KEY idx_notification_logs_guardian_id (guardian_id),
  CONSTRAINT fk_notification_logs_invoices FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE SET NULL,
  CONSTRAINT fk_notification_logs_learners FOREIGN KEY (learner_id) REFERENCES learners(id) ON DELETE SET NULL,
  CONSTRAINT fk_notification_logs_guardians FOREIGN KEY (guardian_id) REFERENCES guardians(id) ON DELETE SET NULL
) ENGINE=InnoDB;
