CREATE TABLE IF NOT EXISTS branches(id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(150) NOT NULL,code VARCHAR(30) UNIQUE,created_at DATETIME);
CREATE TABLE IF NOT EXISTS users(id INT AUTO_INCREMENT PRIMARY KEY,branch_id INT NULL,name VARCHAR(150),email VARCHAR(190) UNIQUE,password VARCHAR(255),role VARCHAR(50),is_active TINYINT DEFAULT 1,created_at DATETIME,INDEX(branch_id));
CREATE TABLE IF NOT EXISTS academic_sessions(id INT AUTO_INCREMENT PRIMARY KEY,branch_id INT NULL,name VARCHAR(120),level VARCHAR(30),start_date DATE,end_date DATE,is_active TINYINT DEFAULT 1,created_at DATETIME);
CREATE TABLE IF NOT EXISTS students(id INT AUTO_INCREMENT PRIMARY KEY,branch_id INT NULL,admission_no VARCHAR(80) UNIQUE,first_name VARCHAR(100),last_name VARCHAR(100),level VARCHAR(30),class_name VARCHAR(80),session_id INT NULL,status VARCHAR(30),created_at DATETIME,INDEX(branch_id),INDEX(session_id));
CREATE TABLE IF NOT EXISTS custom_fields(id INT AUTO_INCREMENT PRIMARY KEY,entity_type VARCHAR(80),group_name VARCHAR(120),label VARCHAR(150),field_key VARCHAR(120),field_type VARCHAR(40),is_required TINYINT DEFAULT 0,options_json TEXT,created_at DATETIME);
CREATE TABLE IF NOT EXISTS schedules(id INT AUTO_INCREMENT PRIMARY KEY,branch_id INT NULL,type VARCHAR(30),title VARCHAR(150),teacher_name VARCHAR(150),class_name VARCHAR(80),subject_name VARCHAR(120),room_name VARCHAR(80),start_at DATETIME,end_at DATETIME,created_at DATETIME,INDEX(start_at));
CREATE TABLE IF NOT EXISTS invoices(id INT AUTO_INCREMENT PRIMARY KEY,branch_id INT NULL,student_id INT,invoice_no VARCHAR(100) UNIQUE,description VARCHAR(255),amount DECIMAL(14,2),status VARCHAR(30),due_date DATE,created_at DATETIME,INDEX(student_id));
CREATE TABLE IF NOT EXISTS audit_logs(id BIGINT AUTO_INCREMENT PRIMARY KEY,user_id INT NULL,branch_id INT NULL,action VARCHAR(80),entity VARCHAR(80),entity_id BIGINT NULL,old_values LONGTEXT,new_values LONGTEXT,ip_address VARCHAR(100),user_agent TEXT,device_name VARCHAR(190),created_at DATETIME,INDEX(user_id),INDEX(created_at));
CREATE TABLE IF NOT EXISTS online_users(user_id INT PRIMARY KEY,branch_id INT NULL,ip_address VARCHAR(100),user_agent TEXT,device_name VARCHAR(190),current_page VARCHAR(190),last_seen_at DATETIME,login_at DATETIME);
CREATE TABLE IF NOT EXISTS integration_jobs(id BIGINT AUTO_INCREMENT PRIMARY KEY,provider VARCHAR(60),entity VARCHAR(80),entity_id BIGINT,payload LONGTEXT,status VARCHAR(30),attempts INT DEFAULT 0,last_error TEXT,created_at DATETIME,updated_at DATETIME);