CREATE DATABASE IF NOT EXISTS educorec_computing CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE educorec_computing;
SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS users (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 full_name VARCHAR(160) NOT NULL,
 username VARCHAR(80) NOT NULL UNIQUE,
 email VARCHAR(190) NULL,
 phone VARCHAR(24) NOT NULL,
 password VARCHAR(255) NOT NULL,
 role ENUM('super_admin','state_distributor','district_distributor','franchise','student') NOT NULL,
 status ENUM('pending','active','inactive','blocked','rejected') NOT NULL DEFAULT 'pending',
 parent_id BIGINT UNSIGNED NULL,
 partner_code VARCHAR(40) NULL UNIQUE,
 application_no VARCHAR(40) NULL,
 state VARCHAR(100) NULL,
 district VARCHAR(100) NULL,
 last_login DATETIME NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 UNIQUE KEY uq_user_email (email),
 UNIQUE KEY uq_user_phone (phone),
 INDEX idx_user_role_status(role,status),
 CONSTRAINT fk_user_parent FOREIGN KEY(parent_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS applications (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL,
 application_no VARCHAR(40) NOT NULL UNIQUE,
 account_type VARCHAR(40) NOT NULL,
 parent_code VARCHAR(40) NULL,
 state VARCHAR(100) NULL,
 district VARCHAR(100) NULL,
 status ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
 reviewed_by BIGINT UNSIGNED NULL,
 reviewed_at DATETIME NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 INDEX idx_app_status(status),
 CONSTRAINT fk_app_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS settings (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 setting_key VARCHAR(100) NOT NULL UNIQUE,
 setting_value TEXT NULL,
 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS wallets (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL UNIQUE,
 balance DECIMAL(14,2) NOT NULL DEFAULT 0.00,
 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 CONSTRAINT fk_wallet_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS wallet_transactions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL,
 reference_no VARCHAR(60) NOT NULL UNIQUE,
 txn_type VARCHAR(40) NOT NULL,
 description VARCHAR(255) NULL,
 credit DECIMAL(14,2) NOT NULL DEFAULT 0.00,
 debit DECIMAL(14,2) NOT NULL DEFAULT 0.00,
 balance_after DECIMAL(14,2) NOT NULL,
 created_by BIGINT UNSIGNED NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 INDEX idx_wallet_txn_user(user_id,created_at),
 CONSTRAINT fk_txn_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS courses (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 course_code VARCHAR(40) NOT NULL UNIQUE,
 course_name VARCHAR(160) NOT NULL,
 fee DECIMAL(12,2) NOT NULL DEFAULT 0,
 duration VARCHAR(80) NULL,
 status ENUM('active','inactive') NOT NULL DEFAULT 'active',
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS students (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 reg_no VARCHAR(50) NOT NULL UNIQUE,
 franchise_id BIGINT UNSIGNED NOT NULL,
 student_name VARCHAR(160) NOT NULL,
 phone VARCHAR(24) NULL,
 course_id BIGINT UNSIGNED NULL,
 course_fee DECIMAL(12,2) NOT NULL DEFAULT 0,
 status ENUM('active','inactive','completed') NOT NULL DEFAULT 'active',
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 INDEX idx_student_franchise(franchise_id),
 CONSTRAINT fk_student_franchise FOREIGN KEY(franchise_id) REFERENCES users(id),
 CONSTRAINT fk_student_course FOREIGN KEY(course_id) REFERENCES courses(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS certificates (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 student_id BIGINT UNSIGNED NOT NULL,
 certificate_no VARCHAR(60) NOT NULL UNIQUE,
 issue_date DATE NOT NULL,
 status ENUM('valid','revoked') NOT NULL DEFAULT 'valid',
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 CONSTRAINT fk_certificate_student FOREIGN KEY(student_id) REFERENCES students(id)
) ENGINE=InnoDB;

INSERT IGNORE INTO settings(setting_key,setting_value) VALUES
('currency','INR'),('currency_symbol','₹'),('language_default','en'),('franchise_fee','0'),('district_distributor_fee','0'),('state_distributor_fee','0'),('commission_franchise','0'),('commission_district','0'),('commission_state','0'),('cashfree_environment','sandbox');

INSERT IGNORE INTO courses(course_code,course_name,fee,duration,status) VALUES
('ADCA','Advanced Diploma in Computer Applications',0,'12 Months','active'),
('DCA','Diploma in Computer Applications',0,'6 Months','active'),
('TALLY','Tally with GST',0,'3 Months','active');