-- ============================================================
-- QR Profile Admin CMS — Database Schema
-- Character Set : utf8mb4
-- Collation     : utf8mb4_unicode_ci
-- Engine        : InnoDB
-- ============================================================

SET NAMES utf8mb4;
SET CHARACTER SET utf8mb4;
SET collation_connection = 'utf8mb4_unicode_ci';
SET foreign_key_checks = 0;

-- ------------------------------------------------------------
-- Create Database
-- ------------------------------------------------------------
CREATE DATABASE IF NOT EXISTS `qr_profile_db`
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

USE `qr_profile_db`;

-- ============================================================
-- Table: admin_users
-- ============================================================
CREATE TABLE IF NOT EXISTS `admin_users` (
    `id`            INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    `name`          VARCHAR(150)    NOT NULL,
    `email`         VARCHAR(191)    NOT NULL,
    `password`      VARCHAR(255)    NOT NULL,
    `role`          ENUM('super_admin','admin') NOT NULL DEFAULT 'admin',
    `is_active`     TINYINT(1)      NOT NULL DEFAULT 1,
    `last_login`    DATETIME        NULL,
    `reset_token`   VARCHAR(255)    NULL,
    `reset_expires` DATETIME        NULL,
    `created_at`    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted_at`    DATETIME        NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_admin_email` (`email`)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci
  COMMENT='Admin panel user accounts';

-- Default super admin — password: Admin@123
INSERT INTO `admin_users` (`name`, `email`, `password`, `role`, `is_active`) VALUES
('Super Admin', 'admin@qrprofile.com',
 '$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi',
 'super_admin', 1);

-- ============================================================
-- Table: profiles  (Digital profile records)
-- ============================================================
CREATE TABLE IF NOT EXISTS `profiles` (
    `id`                INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    `name`              VARCHAR(255)    NOT NULL COMMENT 'Full name — any language/script',
    `company_name`      VARCHAR(255)    NULL,
    `designation`       VARCHAR(255)    NULL,
    `email`             VARCHAR(191)    NULL,
    `mobile`            VARCHAR(20)     NULL,
    `about_us`          LONGTEXT        NULL COMMENT 'Rich text, full Unicode',
    `address`           TEXT            NULL,
    `latitude`          DECIMAL(10,8)   NULL,
    `longitude`         DECIMAL(11,8)   NULL,
    `profile_photo`     VARCHAR(500)    NULL COMMENT 'Relative path under writable/uploads/profiles/',
    -- Social links
    `linkedin`          VARCHAR(500)    NULL,
    `instagram`         VARCHAR(500)    NULL,
    `facebook`          VARCHAR(500)    NULL,
    `twitter`           VARCHAR(500)    NULL,
    `youtube`           VARCHAR(500)    NULL,
    -- System fields
    `slug`              VARCHAR(255)    NULL COMMENT 'URL-safe identifier for QR encoding',
    `is_active`         TINYINT(1)      NOT NULL DEFAULT 1,
    `created_by`        INT UNSIGNED    NOT NULL,
    `created_at`        DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`        DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted_at`        DATETIME        NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_profile_slug` (`slug`),
    CONSTRAINT `fk_profile_creator`
        FOREIGN KEY (`created_by`) REFERENCES `admin_users`(`id`) ON DELETE RESTRICT
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci
  COMMENT='End-user digital profiles';

-- ============================================================
-- Table: profile_media  (Videos & Documents per profile)
-- ============================================================
CREATE TABLE IF NOT EXISTS `profile_media` (
    `id`            INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    `profile_id`    INT UNSIGNED    NOT NULL,
    `media_type`    ENUM('video','document') NOT NULL,
    `title`         VARCHAR(255)    NULL,
    `file_path`     VARCHAR(500)    NULL COMMENT 'Relative path under writable/uploads/',
    `url`           VARCHAR(1000)   NULL COMMENT 'External URL (YouTube, Drive, etc.)',
    `file_size`     INT UNSIGNED    NULL COMMENT 'Size in bytes',
    `mime_type`     VARCHAR(100)    NULL,
    `original_name` VARCHAR(255)    NULL,
    `sort_order`    INT             NOT NULL DEFAULT 0,
    `created_at`    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_media_profile`
        FOREIGN KEY (`profile_id`) REFERENCES `profiles`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci
  COMMENT='Videos and documents attached to profiles';

-- ============================================================
-- Table: qr_codes
-- ============================================================
CREATE TABLE IF NOT EXISTS `qr_codes` (
    `id`            INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    `profile_id`    INT UNSIGNED    NOT NULL,
    `file_path`     VARCHAR(500)    NULL COMMENT 'Generated PNG path',
    `qr_url`        VARCHAR(1000)   NOT NULL COMMENT 'URL encoded in the QR',
    `has_logo`      TINYINT(1)      NOT NULL DEFAULT 0,
    `logo_path`     VARCHAR(500)    NULL,
    `foreground`    VARCHAR(7)      NOT NULL DEFAULT '#000000',
    `background`    VARCHAR(7)      NOT NULL DEFAULT '#FFFFFF',
    `generated_at`  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_qr_profile` (`profile_id`),
    CONSTRAINT `fk_qr_profile`
        FOREIGN KEY (`profile_id`) REFERENCES `profiles`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci
  COMMENT='Generated QR codes for profiles';

-- ============================================================
-- Table: activity_logs  (Audit trail)
-- ============================================================
CREATE TABLE IF NOT EXISTS `activity_logs` (
    `id`            INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    `admin_id`      INT UNSIGNED    NULL,
    `action`        VARCHAR(100)    NOT NULL COMMENT 'e.g. create, update, delete, login',
    `module`        VARCHAR(100)    NOT NULL COMMENT 'e.g. profiles, qr_codes',
    `target_id`     INT UNSIGNED    NULL,
    `description`   TEXT            NULL,
    `ip_address`    VARCHAR(45)     NULL,
    `user_agent`    VARCHAR(500)    NULL,
    `created_at`    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_log_admin`
        FOREIGN KEY (`admin_id`) REFERENCES `admin_users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci
  COMMENT='Admin activity audit trail';

-- ============================================================
-- Table: settings  (App-wide key-value config)
-- ============================================================
CREATE TABLE IF NOT EXISTS `settings` (
    `id`            INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    `key`           VARCHAR(100)    NOT NULL,
    `value`         TEXT            NULL,
    `updated_at`    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_setting_key` (`key`)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci
  COMMENT='System-wide key-value settings';

INSERT INTO `settings` (`key`, `value`) VALUES
('app_name',         'QR Profile CMS'),
('qr_base_url',      'http://localhost/QR-profile/public/profile/'),
('qr_logo_path',     ''),
('default_language', 'en'),
('items_per_page',   '15');

-- ============================================================
-- Performance Indexes
-- ============================================================
CREATE INDEX idx_profiles_name     ON `profiles`(`name`(100));
CREATE INDEX idx_profiles_email    ON `profiles`(`email`);
CREATE INDEX idx_profiles_mobile   ON `profiles`(`mobile`);
CREATE INDEX idx_profiles_active   ON `profiles`(`is_active`);
CREATE INDEX idx_profiles_deleted  ON `profiles`(`deleted_at`);
CREATE INDEX idx_media_profile     ON `profile_media`(`profile_id`);
CREATE INDEX idx_media_type        ON `profile_media`(`media_type`);
CREATE INDEX idx_logs_admin        ON `activity_logs`(`admin_id`);
CREATE INDEX idx_logs_module       ON `activity_logs`(`module`);
CREATE INDEX idx_logs_created      ON `activity_logs`(`created_at`);

SET foreign_key_checks = 1;