-- DigiKinnect schema for MySQL 8.0+ / MariaDB 10.5+
-- Create/select a database in phpMyAdmin before importing this file.

SET NAMES utf8mb4;
SET time_zone = '+00:00';
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(120) NOT NULL,
    email VARCHAR(190) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('admin','staff') NOT NULL DEFAULT 'staff',
    active TINYINT(1) NOT NULL DEFAULT 1,
    last_login_at DATETIME NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE login_attempts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(190) NOT NULL,
    ip_address VARCHAR(45) NOT NULL,
    successful TINYINT(1) NOT NULL DEFAULT 0,
    attempted_at DATETIME NOT NULL,
    INDEX idx_login_rate (ip_address, attempted_at, successful)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE system_settings (
    setting_key VARCHAR(100) PRIMARY KEY,
    setting_value TEXT NULL,
    updated_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO system_settings (setting_key, setting_value, updated_at) VALUES
('organization_name', 'DigiKinnect', NOW()),
('public_headline', 'Big adventures start with a little curiosity.', NOW()),
('public_intro', 'Find a class your child will love, sign up in a few easy steps, and get ready to learn, create, and make new friends.', NOW()),
('contact_email', '', NOW()),
('contact_phone', '', NOW()),
('footer_text', 'Made for curious kids and the grown-ups who cheer them on.', NOW()),
('brand_color', '#6652c7', NOW()),
('company_logo_path', '', NOW());

CREATE TABLE class_groups (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(120) NOT NULL,
    description TEXT NULL,
    active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE classes (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    group_id BIGINT UNSIGNED NULL,
    title VARCHAR(160) NOT NULL,
    description TEXT NULL,
    logo_path VARCHAR(255) NULL,
    location VARCHAR(190) NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    schedule_text VARCHAR(255) NULL,
    total_cost_cents INT UNSIGNED NOT NULL DEFAULT 0,
    deposit_cents INT UNSIGNED NOT NULL DEFAULT 0,
    capacity SMALLINT UNSIGNED NULL,
    min_age TINYINT UNSIGNED NULL,
    max_age TINYINT UNSIGNED NULL,
    registration_open TINYINT(1) NOT NULL DEFAULT 1,
    active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    CONSTRAINT fk_class_group FOREIGN KEY (group_id) REFERENCES class_groups(id) ON DELETE SET NULL,
    INDEX idx_class_dates (start_date, end_date),
    INDEX idx_class_group (group_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE payment_plans (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    class_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(120) NOT NULL,
    description VARCHAR(255) NULL,
    initial_amount_cents INT UNSIGNED NOT NULL DEFAULT 0,
    installment_count TINYINT UNSIGNED NOT NULL DEFAULT 1,
    interval_days SMALLINT UNSIGNED NOT NULL DEFAULT 30,
    active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    CONSTRAINT fk_plan_class FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE CASCADE,
    INDEX idx_plan_class (class_id, active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE parents (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(80) NOT NULL,
    last_name VARCHAR(80) NOT NULL,
    address_line1 VARCHAR(190) NOT NULL,
    address_line2 VARCHAR(190) NULL,
    city VARCHAR(100) NOT NULL,
    state VARCHAR(100) NOT NULL,
    postal_code VARCHAR(20) NOT NULL,
    phone VARCHAR(40) NOT NULL,
    email VARCHAR(190) NOT NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    INDEX idx_parent_email (email),
    INDEX idx_parent_name (last_name, first_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE students (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    parent_id BIGINT UNSIGNED NOT NULL,
    first_name VARCHAR(80) NOT NULL,
    last_name VARCHAR(80) NOT NULL,
    address_line1 VARCHAR(190) NOT NULL,
    address_line2 VARCHAR(190) NULL,
    city VARCHAR(100) NOT NULL,
    state VARCHAR(100) NOT NULL,
    postal_code VARCHAR(20) NOT NULL,
    phone VARCHAR(40) NULL,
    email VARCHAR(190) NULL,
    gender VARCHAR(50) NOT NULL,
    birthdate DATE NOT NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    CONSTRAINT fk_student_parent FOREIGN KEY (parent_id) REFERENCES parents(id) ON DELETE RESTRICT,
    INDEX idx_student_name (last_name, first_name),
    INDEX idx_student_birthdate (birthdate)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE enrollments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    reference VARCHAR(24) NOT NULL UNIQUE,
    class_id BIGINT UNSIGNED NOT NULL,
    student_id BIGINT UNSIGNED NOT NULL,
    parent_id BIGINT UNSIGNED NOT NULL,
    payment_plan_id BIGINT UNSIGNED NULL,
    status ENUM('pending','active','waitlisted','cancelled','completed') NOT NULL DEFAULT 'pending',
    total_cost_cents INT UNSIGNED NOT NULL,
    amount_paid_cents INT UNSIGNED NOT NULL DEFAULT 0,
    balance_cents INT UNSIGNED NOT NULL,
    notes TEXT NULL,
    enrolled_at DATETIME NOT NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    CONSTRAINT fk_enrollment_class FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE RESTRICT,
    CONSTRAINT fk_enrollment_student FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE RESTRICT,
    CONSTRAINT fk_enrollment_parent FOREIGN KEY (parent_id) REFERENCES parents(id) ON DELETE RESTRICT,
    CONSTRAINT fk_enrollment_plan FOREIGN KEY (payment_plan_id) REFERENCES payment_plans(id) ON DELETE SET NULL,
    UNIQUE KEY uq_student_class (student_id, class_id),
    INDEX idx_enrollment_class_status (class_id, status),
    INDEX idx_enrollment_balance (balance_cents)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE enrollment_installments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    enrollment_id BIGINT UNSIGNED NOT NULL,
    sequence_number TINYINT UNSIGNED NOT NULL,
    due_date DATE NOT NULL,
    amount_cents INT UNSIGNED NOT NULL,
    paid_cents INT UNSIGNED NOT NULL DEFAULT 0,
    status ENUM('pending','partial','paid','overdue','waived') NOT NULL DEFAULT 'pending',
    created_at DATETIME NOT NULL,
    CONSTRAINT fk_installment_enrollment FOREIGN KEY (enrollment_id) REFERENCES enrollments(id) ON DELETE CASCADE,
    UNIQUE KEY uq_enrollment_sequence (enrollment_id, sequence_number),
    INDEX idx_installment_due (due_date, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE payment_requests (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    enrollment_id BIGINT UNSIGNED NOT NULL,
    installment_id BIGINT UNSIGNED NULL,
    created_by BIGINT UNSIGNED NOT NULL,
    token_hash CHAR(64) NOT NULL UNIQUE,
    token_hint VARCHAR(12) NOT NULL,
    amount_cents INT UNSIGNED NOT NULL,
    description VARCHAR(255) NOT NULL,
    status ENUM('open','processing','paid','expired','cancelled') NOT NULL DEFAULT 'open',
    expires_at DATETIME NOT NULL,
    processing_at DATETIME NULL,
    paid_at DATETIME NULL,
    created_at DATETIME NOT NULL,
    CONSTRAINT fk_request_enrollment FOREIGN KEY (enrollment_id) REFERENCES enrollments(id) ON DELETE CASCADE,
    CONSTRAINT fk_request_installment FOREIGN KEY (installment_id) REFERENCES enrollment_installments(id) ON DELETE SET NULL,
    CONSTRAINT fk_request_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE RESTRICT,
    INDEX idx_request_enrollment (enrollment_id, status),
    INDEX idx_request_expiry (expires_at, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE payments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    enrollment_id BIGINT UNSIGNED NOT NULL,
    payment_request_id BIGINT UNSIGNED NULL,
    installment_id BIGINT UNSIGNED NULL,
    provider VARCHAR(30) NOT NULL DEFAULT 'stripe',
    stripe_checkout_session_id VARCHAR(255) NULL UNIQUE,
    stripe_payment_intent_id VARCHAR(255) NULL,
    amount_cents INT UNSIGNED NOT NULL,
    refunded_cents INT UNSIGNED NOT NULL DEFAULT 0,
    currency CHAR(3) NOT NULL DEFAULT 'USD',
    status ENUM('pending','succeeded','failed','refunded') NOT NULL DEFAULT 'pending',
    description VARCHAR(255) NOT NULL,
    failure_message VARCHAR(500) NULL,
    paid_at DATETIME NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    CONSTRAINT fk_payment_enrollment FOREIGN KEY (enrollment_id) REFERENCES enrollments(id) ON DELETE RESTRICT,
    CONSTRAINT fk_payment_request FOREIGN KEY (payment_request_id) REFERENCES payment_requests(id) ON DELETE SET NULL,
    CONSTRAINT fk_payment_installment FOREIGN KEY (installment_id) REFERENCES enrollment_installments(id) ON DELETE SET NULL,
    INDEX idx_payment_enrollment (enrollment_id, status),
    INDEX idx_payment_intent (stripe_payment_intent_id),
    INDEX idx_payment_paid_at (paid_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE attendance_sessions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    class_id BIGINT UNSIGNED NOT NULL,
    session_date DATE NOT NULL,
    topic VARCHAR(190) NULL,
    created_by BIGINT UNSIGNED NOT NULL,
    created_at DATETIME NOT NULL,
    CONSTRAINT fk_attendance_session_class FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE CASCADE,
    CONSTRAINT fk_attendance_session_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE RESTRICT,
    UNIQUE KEY uq_class_session_date (class_id, session_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE attendance_records (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    session_id BIGINT UNSIGNED NOT NULL,
    enrollment_id BIGINT UNSIGNED NOT NULL,
    status ENUM('present','absent','late','excused') NOT NULL,
    note VARCHAR(255) NULL,
    recorded_by BIGINT UNSIGNED NOT NULL,
    recorded_at DATETIME NOT NULL,
    CONSTRAINT fk_attendance_record_session FOREIGN KEY (session_id) REFERENCES attendance_sessions(id) ON DELETE CASCADE,
    CONSTRAINT fk_attendance_record_enrollment FOREIGN KEY (enrollment_id) REFERENCES enrollments(id) ON DELETE CASCADE,
    CONSTRAINT fk_attendance_record_user FOREIGN KEY (recorded_by) REFERENCES users(id) ON DELETE RESTRICT,
    UNIQUE KEY uq_session_enrollment (session_id, enrollment_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE communications (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    created_by BIGINT UNSIGNED NOT NULL,
    class_id BIGINT UNSIGNED NULL,
    group_id BIGINT UNSIGNED NULL,
    audience ENUM('class','group','individual') NOT NULL,
    channel ENUM('email') NOT NULL DEFAULT 'email',
    subject VARCHAR(190) NOT NULL,
    body_html TEXT NOT NULL,
    status ENUM('draft','sending','sent','partially_failed','failed') NOT NULL DEFAULT 'draft',
    recipient_count INT UNSIGNED NOT NULL DEFAULT 0,
    sent_at DATETIME NULL,
    created_at DATETIME NOT NULL,
    CONSTRAINT fk_communication_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE RESTRICT,
    CONSTRAINT fk_communication_class FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE SET NULL,
    CONSTRAINT fk_communication_group FOREIGN KEY (group_id) REFERENCES class_groups(id) ON DELETE SET NULL,
    INDEX idx_communication_sent (sent_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE communication_recipients (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    communication_id BIGINT UNSIGNED NOT NULL,
    parent_id BIGINT UNSIGNED NULL,
    email VARCHAR(190) NOT NULL,
    name VARCHAR(160) NOT NULL,
    status ENUM('pending','sent','failed') NOT NULL DEFAULT 'pending',
    error_message VARCHAR(500) NULL,
    sent_at DATETIME NULL,
    CONSTRAINT fk_recipient_communication FOREIGN KEY (communication_id) REFERENCES communications(id) ON DELETE CASCADE,
    CONSTRAINT fk_recipient_parent FOREIGN KEY (parent_id) REFERENCES parents(id) ON DELETE SET NULL,
    INDEX idx_recipient_status (communication_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE webhook_events (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    stripe_event_id VARCHAR(255) NOT NULL UNIQUE,
    event_type VARCHAR(120) NOT NULL,
    received_at DATETIME NOT NULL,
    processed_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE audit_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NULL,
    action VARCHAR(100) NOT NULL,
    entity_type VARCHAR(100) NOT NULL,
    entity_id BIGINT UNSIGNED NULL,
    details_json JSON NULL,
    ip_address VARCHAR(45) NULL,
    created_at DATETIME NOT NULL,
    CONSTRAINT fk_audit_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_audit_entity (entity_type, entity_id),
    INDEX idx_audit_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;