-- Daroyar MySQL schema for cPanel / phpMyAdmin
-- Database: hiddene3_darooyar
-- Charset: utf8mb4

SET NAMES utf8mb4;
SET time_zone = '+03:30';

CREATE TABLE IF NOT EXISTS devices (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    device_uuid VARCHAR(128) NOT NULL,
    device_name VARCHAR(190) NULL,
    app_version VARCHAR(50) NULL,
    created_at_ms BIGINT NOT NULL,
    last_seen_at_ms BIGINT NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_devices_uuid (device_uuid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS medicines (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    device_id BIGINT UNSIGNED NOT NULL,
    local_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    unit VARCHAR(120) NOT NULL,
    dosage_amount DECIMAL(10,3) NOT NULL DEFAULT 1,
    icon_key VARCHAR(120) NULL,
    medicine_time VARCHAR(10) NULL,
    schedule_type VARCHAR(50) NULL,
    selected_week_days VARCHAR(80) NULL,
    jalali_created_date VARCHAR(20) NULL,
    gregorian_created_date VARCHAR(20) NULL,
    specific_date VARCHAR(20) NULL,
    jalali_start_date VARCHAR(20) NULL,
    start_date VARCHAR(20) NULL,
    is_icon_manually_selected TINYINT(1) NOT NULL DEFAULT 0,
    created_at_ms BIGINT NULL,
    updated_at_ms BIGINT NULL,
    schedule_mode VARCHAR(30) NOT NULL DEFAULT 'single',
    interval_hours INT NOT NULL DEFAULT 6,
    interval_start_minutes INT NOT NULL DEFAULT 480,
    interval_end_minutes INT NOT NULL DEFAULT 1439,
    schedule_version INT NOT NULL DEFAULT 1,
    is_deleted TINYINT(1) NOT NULL DEFAULT 0,
    server_updated_at_ms BIGINT NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_medicines_device_local (device_id, local_id),
    KEY idx_medicines_device_updated (device_id, server_updated_at_ms),
    KEY idx_medicines_device_deleted (device_id, is_deleted),
    CONSTRAINT fk_medicines_device FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS medicine_schedule_versions (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    device_id BIGINT UNSIGNED NOT NULL,
    local_id INT NOT NULL,
    medicine_local_id INT NOT NULL,
    mode VARCHAR(30) NOT NULL,
    valid_from VARCHAR(20) NOT NULL,
    valid_until VARCHAR(20) NULL,
    interval_hours INT NOT NULL DEFAULT 6,
    start_minutes INT NOT NULL DEFAULT 480,
    end_minutes INT NOT NULL DEFAULT 1439,
    created_at_ms BIGINT NULL,
    server_updated_at_ms BIGINT NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_schedule_versions_device_local (device_id, local_id),
    KEY idx_schedule_versions_device_medicine (device_id, medicine_local_id),
    KEY idx_schedule_versions_updated (device_id, server_updated_at_ms),
    CONSTRAINT fk_schedule_versions_device FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS medicine_schedule_times (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    device_id BIGINT UNSIGNED NOT NULL,
    local_id INT NOT NULL,
    medicine_local_id INT NOT NULL,
    schedule_version_local_id INT NOT NULL,
    hour INT NOT NULL,
    minute INT NOT NULL,
    minutes_from_midnight INT NOT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    created_at_ms BIGINT NULL,
    server_updated_at_ms BIGINT NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_schedule_times_device_local (device_id, local_id),
    UNIQUE KEY uq_schedule_times_device_version_minute (device_id, medicine_local_id, schedule_version_local_id, minutes_from_midnight),
    KEY idx_schedule_times_updated (device_id, server_updated_at_ms),
    CONSTRAINT fk_schedule_times_device FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS medicine_occurrences (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    device_id BIGINT UNSIGNED NOT NULL,
    local_id INT NOT NULL,
    medicine_local_id INT NOT NULL,
    schedule_version_local_id INT NOT NULL,
    local_date VARCHAR(20) NOT NULL,
    scheduled_epoch_millis BIGINT NOT NULL,
    scheduled_hour INT NOT NULL,
    scheduled_minute INT NOT NULL,
    status VARCHAR(30) NOT NULL DEFAULT 'pending',
    taken_at_ms BIGINT NULL,
    snoozed_until_ms BIGINT NULL,
    created_at_ms BIGINT NULL,
    updated_at_ms BIGINT NULL,
    server_updated_at_ms BIGINT NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_occurrences_device_local (device_id, local_id),
    UNIQUE KEY uq_occurrences_device_medicine_time (device_id, medicine_local_id, scheduled_epoch_millis),
    KEY idx_occurrences_device_date (device_id, local_date),
    KEY idx_occurrences_device_status (device_id, status),
    KEY idx_occurrences_updated (device_id, server_updated_at_ms),
    CONSTRAINT fk_occurrences_device FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS medicine_intakes (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    device_id BIGINT UNSIGNED NOT NULL,
    local_id INT NOT NULL,
    medicine_local_id INT NOT NULL,
    dosage_date VARCHAR(20) NOT NULL,
    dosage_time VARCHAR(10) NOT NULL,
    status VARCHAR(30) NOT NULL,
    timestamp_ms BIGINT NULL,
    taken_at_ms BIGINT NULL,
    snoozed_until_ms BIGINT NULL,
    selected_action_time VARCHAR(10) NULL,
    scheduled_epoch_millis BIGINT NULL,
    snooze_count INT NOT NULL DEFAULT 0,
    action_source VARCHAR(80) NULL,
    updated_at_ms BIGINT NULL,
    server_updated_at_ms BIGINT NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_intakes_device_local (device_id, local_id),
    KEY idx_intakes_device_medicine_date (device_id, medicine_local_id, dosage_date),
    KEY idx_intakes_updated (device_id, server_updated_at_ms),
    CONSTRAINT fk_intakes_device FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS user_reward_points (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    device_id BIGINT UNSIGNED NOT NULL,
    local_id INT NOT NULL,
    occurrence_local_id INT NULL,
    medicine_local_id INT NOT NULL,
    points INT NOT NULL,
    reason VARCHAR(80) NOT NULL,
    created_at_ms BIGINT NULL,
    local_date VARCHAR(20) NOT NULL,
    server_updated_at_ms BIGINT NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_reward_points_device_local (device_id, local_id),
    UNIQUE KEY uq_reward_points_device_occurrence_reason (device_id, occurrence_local_id, reason),
    KEY idx_reward_points_device_date (device_id, local_date),
    KEY idx_reward_points_updated (device_id, server_updated_at_ms),
    CONSTRAINT fk_reward_points_device FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS medicine_day_exceptions (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    device_id BIGINT UNSIGNED NOT NULL,
    local_id INT NOT NULL,
    medicine_local_id INT NOT NULL,
    exception_date VARCHAR(20) NOT NULL,
    timestamp_ms BIGINT NULL,
    server_updated_at_ms BIGINT NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_day_exceptions_device_local (device_id, local_id),
    UNIQUE KEY uq_day_exceptions_device_medicine_date (device_id, medicine_local_id, exception_date),
    KEY idx_day_exceptions_updated (device_id, server_updated_at_ms),
    CONSTRAINT fk_day_exceptions_device FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS app_settings (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    device_id BIGINT UNSIGNED NOT NULL,
    setting_key VARCHAR(120) NOT NULL,
    value_json LONGTEXT NULL,
    client_updated_at_ms BIGINT NULL,
    server_updated_at_ms BIGINT NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_settings_device_key (device_id, setting_key),
    KEY idx_settings_updated (device_id, server_updated_at_ms),
    CONSTRAINT fk_settings_device FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;