-- ============================================================================
--  FamilyGuard - MySQL Schema
--  Run this in phpMyAdmin (or: mysql < schema.sql) once, before first use.
-- ============================================================================

-- ---------------------------------------------------------------------------
--  Users: parent accounts that control one or more child devices.
--  Children are DEVICES, not users. Only parents log in on web/app dashboards.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS users (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name            VARCHAR(120)  NOT NULL,
    email           VARCHAR(190)  NOT NULL UNIQUE,
    password_hash   VARCHAR(255)  NOT NULL,
    theme           ENUM('light','dark','system') NOT NULL DEFAULT 'system',
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------------
--  Devices: one row per child phone. Linked to a parent (owner_id).
--  A child device is enrolled via a one-time 6 digit pairing CODE.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS devices (
    id               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    owner_id         INT UNSIGNED NOT NULL,
    name             VARCHAR(120)  NOT NULL,          -- friendly name, e.g. "Sophie's phone"
    pairing_code     CHAR(6)       NOT NULL,          -- 6-digit enrol code (shown to parent)
    pairing_used     TINYINT(1)    NOT NULL DEFAULT 0,
    device_token     CHAR(64)      NULL,               -- per-device secret issued after pairing
    platform         VARCHAR(40)   NOT NULL DEFAULT 'android', -- android
    model            VARCHAR(120)  NULL,
    os_version       VARCHAR(64)   NULL,
    app_version      VARCHAR(32)   NULL,
    vpn_enabled      TINYINT(1)    NOT NULL DEFAULT 0,   -- web filter on? (Phase 5)
    nls_enabled      TINYINT(1)    NOT NULL DEFAULT 0,   -- notification capture? (Phase 5)
    owner_enabled    TINYINT(1)    NOT NULL DEFAULT 0,   -- device-owner mode? (Phase X)
    screen_time_paused TINYINT(1)  NOT NULL DEFAULT 0,   -- kill-switch (Phase X)
    banned_apps      TEXT          NULL,               -- JSON array of banned pkg ids (Phase X)
    last_seen_at     DATETIME      NULL,               -- last successful ping
    created_at       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_dev_owner FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE INDEX idx_dev_owner ON devices (owner_id);

-- ---------------------------------------------------------------------------
--  usage_logs: per-app screen-time, bucketed (hourly by default).
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS usage_logs (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    device_id   INT UNSIGNED NOT NULL,
    package_id  VARCHAR(190) NOT NULL,
    app_name    VARCHAR(190) NOT NULL DEFAULT '',
    minutes     SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    bucket_utc  DATETIME NOT NULL,                   -- rounding start (e.g. top of hour)
    recorded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_ul_dev FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE,
    UNIQUE KEY uq_bucket (device_id, package_id, bucket_utc)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------------
--  location_logs: point-in-time location fixes + optional history trail.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS location_logs (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    device_id   INT UNSIGNED NOT NULL,
    latitude    DECIMAL(10,7) NOT NULL,
    longitude   DECIMAL(10,7) NOT NULL,
    accuracy_m  FLOAT DEFAULT NULL,
    speed_kmh   FLOAT DEFAULT NULL,
    provider     ENUM('gps','network','passive','fused') NOT NULL DEFAULT 'gps',
    captured_at DATETIME NOT NULL,
    recorded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_ll_dev FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE INDEX idx_ll_dev_cap ON location_logs (device_id, captured_at);

-- ---------------------------------------------------------------------------
--  event_logs: installs, uninstalls, screen on/off, boot, network changes.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS event_logs (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    device_id   INT UNSIGNED NOT NULL,
    event_type  ENUM('app_installed','app_uninstalled','screen_on','screen_off',
                     'boot_completed','network_change','vpn_state','nls_captured',
                     'owner_enabled','screen_time_paused') NOT NULL,
    package_id  VARCHAR(190) NULL,
    app_name    VARCHAR(190) NULL,
    detail      TEXT NULL,
    occurred_at DATETIME NOT NULL,
    recorded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_el_dev FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE INDEX idx_el_dev_occ ON event_logs (device_id, occurred_at);

-- ---------------------------------------------------------------------------
--  commands: outbox for remote actions (child app polls & executes).
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS commands (
    id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    device_id  INT UNSIGNED NOT NULL,
    kind       ENUM('ping','lock','search','alert','pause_screen_time',
                    'unpause_screen_time','enable_vpn','disable_vpn')
              NOT NULL DEFAULT 'ping',
    payload    TEXT NULL,
    status     ENUM('queued','acked','done','failed') NOT NULL DEFAULT 'queued',
    issued_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    acked_at   DATETIME NULL,
    done_at    DATETIME NULL,
    CONSTRAINT fk_cmd_dev FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE INDEX idx_cmd_dev_status ON commands (device_id, status);

-- ---------------------------------------------------------------------------
--  alerts: filter hits, battery, erratic movement, pairing, breaches...
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS alerts (
    id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    device_id  INT UNSIGNED NOT NULL,
    severity   ENUM('info','warning','critical') NOT NULL DEFAULT 'info',
    title      VARCHAR(190) NOT NULL,
    detail     TEXT NULL,
    acknowledged TINYINT(1) NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_al_dev FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE INDEX idx_al_dev_created ON alerts (device_id, created_at);

-- ---------------------------------------------------------------------------
--  message_logs: text captured from SMS / messaging-app notifications.
--  direction: 'in' received, 'out' sent. body may be partial (Android limits).
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS message_logs (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    device_id   INT UNSIGNED NOT NULL,
    app_package VARCHAR(190) NOT NULL DEFAULT '',      -- e.g. com.whatsapp
    sender      VARCHAR(190) NULL,                      -- contact name / phone
    recipient   VARCHAR(190) NULL,                      -- local user if 'out'
    body        TEXT NOT NULL,
    direction   ENUM('in','out','unknown') NOT NULL DEFAULT 'unknown',
    captured_at DATETIME NOT NULL,
    recorded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_ml_dev FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE INDEX idx_ml_dev_cap ON message_logs (device_id, captured_at);

-- ---------------------------------------------------------------------------
--  call_logs: best-effort call history (READ_CALL_LOG; blocked on Android 9+).
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS call_logs (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    device_id   INT UNSIGNED NOT NULL,
    phone_number VARCHAR(40) NULL,
    contact_name VARCHAR(190) NULL,
    call_type   ENUM('in','out','missed','rejected','blocked','unknown') NOT NULL DEFAULT 'unknown',
    duration_sec INT UNSIGNED NOT NULL DEFAULT 0,
    started_at  DATETIME NOT NULL,
    recorded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_cl_dev FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE INDEX idx_cl_dev_start ON call_logs (device_id, started_at);

-- ---------------------------------------------------------------------------
--  media_logs: metadata for photos/videos on the device (no content uploaded).
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS media_logs (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    device_id   INT UNSIGNED NOT NULL,
    media_type  ENUM('image','video','audio','other') NOT NULL DEFAULT 'other',
    file_name   VARCHAR(255) NULL,
    mime        VARCHAR(120) NULL,
    size_bytes  BIGINT UNSIGNED NOT NULL DEFAULT 0,
    created_at  DATETIME NULL,                          -- file creation time
    captured_at DATETIME NOT NULL,
    recorded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_mdl_dev FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE INDEX idx_mdl_dev_cap ON media_logs (device_id, captured_at);

-- ---------------------------------------------------------------------------
--  keywords: parent-configured terms scanned against captured message text.
--  A match produces an 'alert' on the owning device. Scoped to a parent owner
--  (not a single device) so all of their children share the same list.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS keywords (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    owner_id   INT UNSIGNED NOT NULL,
    keyword    VARCHAR(190) NOT NULL,
    severity   ENUM('info','warning','critical') NOT NULL DEFAULT 'warning',
    active     TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_kw_owner FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY uq_kw (owner_id, keyword)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;