-- =====================================================================
-- SUPER PHARMA - Future Modules (Doctor / Lab / Healthcare Marketplace)
-- Sections 15-17. Created now so foreign keys/relations don't require a
-- core rebuild later, but NOT wired into any Phase-1 controller/route.
-- Safe to run alongside 001, or skip entirely for a leaner Phase-1 DB.
-- =====================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE doctors (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    mobile VARCHAR(10) NOT NULL UNIQUE,
    email VARCHAR(150) NULL,
    password_hash VARCHAR(255) NOT NULL,
    specialization VARCHAR(150) NULL,
    medical_registration_number VARCHAR(50) NULL,
    consultation_fee DECIMAL(10,2) NULL,
    city_id INT UNSIGNED NULL,
    verification_status ENUM('pending', 'verified', 'rejected') NOT NULL DEFAULT 'pending',
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (city_id) REFERENCES cities(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE doctor_availability (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    doctor_id INT UNSIGNED NOT NULL,
    day_of_week TINYINT UNSIGNED NOT NULL, -- 0=Sunday
    start_time TIME NOT NULL,
    end_time TIME NOT NULL,
    slot_minutes SMALLINT UNSIGNED NOT NULL DEFAULT 15,
    consult_mode ENUM('clinic', 'video', 'both') NOT NULL DEFAULT 'both',
    FOREIGN KEY (doctor_id) REFERENCES doctors(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE appointments (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    doctor_id INT UNSIGNED NOT NULL,
    customer_id INT UNSIGNED NOT NULL,
    scheduled_at DATETIME NOT NULL,
    consult_mode ENUM('clinic', 'video') NOT NULL DEFAULT 'clinic',
    status ENUM('booked', 'confirmed', 'completed', 'cancelled', 'no_show') NOT NULL DEFAULT 'booked',
    digital_prescription_path VARCHAR(500) NULL,
    follow_up_of INT UNSIGNED NULL,
    fee_amount DECIMAL(10,2) NULL,
    payment_status ENUM('pending', 'paid', 'refunded') NOT NULL DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (doctor_id) REFERENCES doctors(id),
    FOREIGN KEY (customer_id) REFERENCES customers(id),
    FOREIGN KEY (follow_up_of) REFERENCES appointments(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE labs (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    mobile VARCHAR(10) NOT NULL UNIQUE,
    email VARCHAR(150) NULL,
    password_hash VARCHAR(255) NOT NULL,
    license_number VARCHAR(50) NULL,
    city_id INT UNSIGNED NULL,
    verification_status ENUM('pending', 'verified', 'rejected') NOT NULL DEFAULT 'pending',
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (city_id) REFERENCES cities(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE lab_tests (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    lab_id INT UNSIGNED NOT NULL,
    name VARCHAR(200) NOT NULL,
    is_package TINYINT(1) NOT NULL DEFAULT 0,
    price DECIMAL(10,2) NOT NULL,
    home_collection_available TINYINT(1) NOT NULL DEFAULT 1,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    FOREIGN KEY (lab_id) REFERENCES labs(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE lab_bookings (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    lab_id INT UNSIGNED NOT NULL,
    lab_test_id INT UNSIGNED NOT NULL,
    customer_id INT UNSIGNED NOT NULL,
    address_id INT UNSIGNED NULL, -- for home collection
    slot_at DATETIME NOT NULL,
    phlebotomist_id INT UNSIGNED NULL, -- could map to delivery_partners or a separate staff table later
    status ENUM('booked', 'sample_collected', 'processing', 'report_ready', 'cancelled') NOT NULL DEFAULT 'booked',
    report_path VARCHAR(500) NULL,
    payment_status ENUM('pending', 'paid', 'refunded') NOT NULL DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (lab_id) REFERENCES labs(id),
    FOREIGN KEY (lab_test_id) REFERENCES lab_tests(id),
    FOREIGN KEY (customer_id) REFERENCES customers(id),
    FOREIGN KEY (address_id) REFERENCES addresses(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Membership / health packages (section 17)
CREATE TABLE memberships (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    duration_days INT UNSIGNED NOT NULL,
    benefits_json TEXT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE customer_memberships (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_id INT UNSIGNED NOT NULL,
    membership_id INT UNSIGNED NOT NULL,
    starts_at DATE NOT NULL,
    ends_at DATE NOT NULL,
    status ENUM('active', 'expired', 'cancelled') NOT NULL DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id),
    FOREIGN KEY (membership_id) REFERENCES memberships(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;