-- ============================================================
--  Chart-Board — MySQL Database Schema
--  Version : 1.0.0
--  Engine  : InnoDB | Charset: utf8mb4 | Collation: utf8mb4_unicode_ci
-- ============================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
SET SQL_MODE = 'NO_ENGINE_SUBSTITUTION';

CREATE DATABASE IF NOT EXISTS `chartboard`
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

USE `chartboard`;


-- ============================================================
-- TABLE: users
-- Stores all registered user accounts
-- ============================================================
CREATE TABLE `users` (
    `id`                INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    `name`              VARCHAR(150)    NOT NULL,
    `email`             VARCHAR(255)    NOT NULL UNIQUE,
    `password`          VARCHAR(255)    NOT NULL COMMENT 'bcrypt hashed',
    `avatar`            VARCHAR(500)    NULL      DEFAULT NULL COMMENT 'URL or file path',
    `role`              ENUM('superadmin','user') NOT NULL DEFAULT 'user',
    `email_verified`    TINYINT(1)      NOT NULL DEFAULT 0,
    `verify_token`      VARCHAR(255)    NULL      DEFAULT NULL,
    `reset_token`       VARCHAR(255)    NULL      DEFAULT NULL,
    `reset_token_expiry` DATETIME       NULL      DEFAULT NULL,
    `api_token`         VARCHAR(255)    NULL      DEFAULT NULL UNIQUE COMMENT 'Personal API token',
    `is_active`         TINYINT(1)      NOT NULL DEFAULT 1,
    `theme_preference`  VARCHAR(20)     NOT NULL DEFAULT 'light' COMMENT 'UI theme: light, dark, system',
    `last_login_at`     DATETIME        NULL      DEFAULT 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      DEFAULT NULL COMMENT 'Soft delete',
    PRIMARY KEY (`id`),
    INDEX `idx_users_email`     (`email`),
    INDEX `idx_users_api_token` (`api_token`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='All registered Chart-Board users';


-- ============================================================
-- TABLE: workspaces
-- Top-level container for data sources, charts, dashboards
-- ============================================================
CREATE TABLE `workspaces` (
    `id`               INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    `name`             VARCHAR(150)  NOT NULL,
    `slug`             VARCHAR(160)  NOT NULL UNIQUE COMMENT 'URL-friendly identifier',
    `description`      TEXT          NULL DEFAULT NULL,
    `logo`             VARCHAR(500)  NULL DEFAULT NULL,
    `timezone`         VARCHAR(80)   NOT NULL DEFAULT 'UTC',
    `default_refresh`  INT UNSIGNED  NOT NULL DEFAULT 300 COMMENT 'Default refresh in seconds',
    `owner_id`         INT UNSIGNED  NOT NULL,
    `is_active`        TINYINT(1)    NOT NULL DEFAULT 1,
    `created_at`       DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`       DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted_at`       DATETIME      NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    INDEX `idx_workspaces_owner` (`owner_id`),
    CONSTRAINT `fk_workspaces_owner`
        FOREIGN KEY (`owner_id`) REFERENCES `users` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Workspace — team or project level grouping';


-- ============================================================
-- TABLE: workspace_members
-- Many-to-many: users ↔ workspaces with role
-- ============================================================
CREATE TABLE `workspace_members` (
    `id`           INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `workspace_id` INT UNSIGNED NOT NULL,
    `user_id`      INT UNSIGNED NOT NULL,
    `role`         ENUM('admin','editor','viewer') NOT NULL DEFAULT 'viewer',
    `invited_by`   INT UNSIGNED NULL DEFAULT NULL,
    `joined_at`    DATETIME     NULL DEFAULT NULL,
    `created_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_workspace_member` (`workspace_id`, `user_id`),
    CONSTRAINT `fk_wm_workspace`
        FOREIGN KEY (`workspace_id`) REFERENCES `workspaces` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_wm_user`
        FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_wm_invited_by`
        FOREIGN KEY (`invited_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Workspace membership and roles';


-- ============================================================
-- TABLE: workspace_invitations
-- Pending email invitations to join a workspace
-- ============================================================
CREATE TABLE `workspace_invitations` (
    `id`           INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    `workspace_id` INT UNSIGNED  NOT NULL,
    `invited_by`   INT UNSIGNED  NOT NULL,
    `email`        VARCHAR(255)  NOT NULL,
    `role`         ENUM('admin','editor','viewer') NOT NULL DEFAULT 'viewer',
    `token`        VARCHAR(255)  NOT NULL UNIQUE,
    `accepted`     TINYINT(1)    NOT NULL DEFAULT 0,
    `expires_at`   DATETIME      NOT NULL,
    `created_at`   DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_invitations_token` (`token`),
    CONSTRAINT `fk_inv_workspace`
        FOREIGN KEY (`workspace_id`) REFERENCES `workspaces` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_inv_invited_by`
        FOREIGN KEY (`invited_by`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Pending workspace invitations via email';


-- ============================================================
-- TABLE: data_sources
-- Connection configurations for databases, APIs, CSV files
-- ============================================================
CREATE TABLE `data_sources` (
    `id`             INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    `workspace_id`   INT UNSIGNED    NOT NULL,
    `name`           VARCHAR(150)    NOT NULL,
    `type`           ENUM('mysql','postgresql','mongodb','rest_api','csv') NOT NULL,
    `host`           VARCHAR(255)    NULL DEFAULT NULL,
    `port`           SMALLINT UNSIGNED NULL DEFAULT NULL,
    `database_name`  VARCHAR(150)    NULL DEFAULT NULL,
    `username`       VARCHAR(150)    NULL DEFAULT NULL,
    `password`       TEXT            NULL DEFAULT NULL COMMENT 'AES-256 encrypted',
    `connection_uri` TEXT            NULL DEFAULT NULL COMMENT 'For MongoDB or DSN-style',
    `ssl_enabled`    TINYINT(1)      NOT NULL DEFAULT 0,
    `ssl_ca`         TEXT            NULL DEFAULT NULL,
    -- REST API fields
    `api_base_url`   VARCHAR(500)    NULL DEFAULT NULL,
    `api_method`     ENUM('GET','POST') NULL DEFAULT 'GET',
    `api_auth_type`  ENUM('none','bearer','basic','api_key') NULL DEFAULT 'none',
    `api_auth_value` TEXT            NULL DEFAULT NULL COMMENT 'Encrypted token/password',
    `api_headers`    JSON            NULL DEFAULT NULL COMMENT 'Static headers as key-value JSON',
    -- CSV fields
    `csv_file_path`  VARCHAR(500)    NULL DEFAULT NULL,
    `csv_delimiter`  CHAR(1)         NULL DEFAULT ',',
    -- Status
    `last_tested_at` DATETIME        NULL DEFAULT NULL,
    `status`         ENUM('untested','connected','failed') NOT NULL DEFAULT 'untested',
    `error_message`  TEXT            NULL DEFAULT NULL,
    `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 DEFAULT NULL,
    PRIMARY KEY (`id`),
    INDEX `idx_ds_workspace` (`workspace_id`),
    CONSTRAINT `fk_ds_workspace`
        FOREIGN KEY (`workspace_id`) REFERENCES `workspaces` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_ds_created_by`
        FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Data source connection configurations';


-- ============================================================
-- TABLE: saved_queries
-- Reusable queries (SQL or API-mapped) per data source
-- ============================================================
CREATE TABLE `saved_queries` (
    `id`              INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    `workspace_id`    INT UNSIGNED  NOT NULL,
    `data_source_id`  INT UNSIGNED  NOT NULL,
    `name`            VARCHAR(150)  NOT NULL,
    `description`     TEXT          NULL DEFAULT NULL,
    `query_type`      ENUM('visual','raw_sql','api') NOT NULL DEFAULT 'raw_sql',
    `raw_sql`         TEXT          NULL DEFAULT NULL,
    `visual_config`   JSON          NULL DEFAULT NULL COMMENT 'Visual builder settings (table, filters, columns)',
    `api_endpoint`    VARCHAR(500)  NULL DEFAULT NULL COMMENT 'Override base URL for specific query',
    `api_params`      JSON          NULL DEFAULT NULL COMMENT 'Query params / body',
    `response_path`   VARCHAR(255)  NULL DEFAULT NULL COMMENT 'JSON path to data array e.g. data.results',
    `field_map`       JSON          NULL DEFAULT NULL COMMENT 'Map response fields to aliases',
    `cache_ttl`       INT UNSIGNED  NOT NULL DEFAULT 0 COMMENT 'Cache result for N seconds (0 = no cache)',
    `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 DEFAULT NULL,
    PRIMARY KEY (`id`),
    INDEX `idx_sq_workspace`   (`workspace_id`),
    INDEX `idx_sq_datasource`  (`data_source_id`),
    CONSTRAINT `fk_sq_workspace`
        FOREIGN KEY (`workspace_id`) REFERENCES `workspaces` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_sq_datasource`
        FOREIGN KEY (`data_source_id`) REFERENCES `data_sources` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_sq_created_by`
        FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Reusable saved queries for charts';


-- ============================================================
-- TABLE: query_variables
-- Variable definitions detected/configured per saved query
-- ============================================================
CREATE TABLE `query_variables` (
    `id`              INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    `saved_query_id`  INT UNSIGNED  NOT NULL,
    `workspace_id`    INT UNSIGNED  NOT NULL,
    `name`            VARCHAR(100)  NOT NULL COMMENT 'Variable token name without braces',
    `label`           VARCHAR(120)  NULL DEFAULT NULL COMMENT 'Display label for form widgets',
    `type`            ENUM('text','number','date','date_range','select','multi_select') NOT NULL DEFAULT 'text',
    `default_value`   TEXT          NULL DEFAULT NULL,
    `options_json`    JSON          NULL DEFAULT NULL COMMENT 'Option list for select/multi_select',
    `is_required`     TINYINT(1)    NOT NULL DEFAULT 0,
    `sort_order`      SMALLINT UNSIGNED NOT NULL DEFAULT 1,
    `created_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_qv_query_name` (`saved_query_id`, `name`),
    INDEX `idx_qv_workspace` (`workspace_id`),
    INDEX `idx_qv_query` (`saved_query_id`),
    CONSTRAINT `fk_qv_saved_query`
        FOREIGN KEY (`saved_query_id`) REFERENCES `saved_queries` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_qv_workspace`
        FOREIGN KEY (`workspace_id`) REFERENCES `workspaces` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Variable definitions for parameterized saved queries';


-- ============================================================
-- TABLE: charts
-- Individual chart definitions
-- ============================================================
CREATE TABLE `charts` (
    `id`               INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    `workspace_id`     INT UNSIGNED  NOT NULL,
    `data_source_id`   INT UNSIGNED  NOT NULL,
    `saved_query_id`   INT UNSIGNED  NULL DEFAULT NULL,
    `name`             VARCHAR(200)  NOT NULL,
    `description`      TEXT          NULL DEFAULT NULL,
    `chart_type`       ENUM(
                           'bar','line','area','pie','donut',
                           'scatter','table','kpi_card','funnel',
                           'gauge','heatmap','combo',
                           'spline','stepline','radar','bubble','polar_area'
                       ) NOT NULL,
    `query_type`       ENUM('visual','raw_sql','api') NOT NULL DEFAULT 'raw_sql',
    `raw_sql`          TEXT          NULL DEFAULT NULL,
    `visual_config`    JSON          NULL DEFAULT NULL,
    `api_endpoint`     VARCHAR(500)  NULL DEFAULT NULL,
    `api_params`       JSON          NULL DEFAULT NULL,
    `response_path`    VARCHAR(255)  NULL DEFAULT NULL,
    `field_map`        JSON          NULL DEFAULT NULL,
    -- Axis & field mapping
    `x_field`          VARCHAR(150)  NULL DEFAULT NULL,
    `y_field`          VARCHAR(150)  NULL DEFAULT NULL,
    `group_field`      VARCHAR(150)  NULL DEFAULT NULL,
    `value_field`      VARCHAR(150)  NULL DEFAULT NULL COMMENT 'Used for KPI/Gauge',
    -- Display settings
    `display_config`   JSON          NULL DEFAULT NULL COMMENT 'Colors, labels, legend, formatting options',
    `refresh_interval` INT UNSIGNED  NOT NULL DEFAULT 0 COMMENT 'Auto-refresh in seconds (0 = manual)',
    `cache_ttl`        INT UNSIGNED  NOT NULL DEFAULT 300 COMMENT 'Cache result TTL in seconds',
    `is_public`        TINYINT(1)    NOT NULL DEFAULT 0,
    `public_token`     VARCHAR(100)  NULL DEFAULT NULL UNIQUE,
    `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 DEFAULT NULL,
    PRIMARY KEY (`id`),
    INDEX `idx_charts_workspace`  (`workspace_id`),
    INDEX `idx_charts_datasource` (`data_source_id`),
    INDEX `idx_charts_public`     (`public_token`),
    CONSTRAINT `fk_charts_workspace`
        FOREIGN KEY (`workspace_id`) REFERENCES `workspaces` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_charts_datasource`
        FOREIGN KEY (`data_source_id`) REFERENCES `data_sources` (`id`) ON DELETE RESTRICT,
    CONSTRAINT `fk_charts_query`
        FOREIGN KEY (`saved_query_id`) REFERENCES `saved_queries` (`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_charts_created_by`
        FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Chart definitions with query and display configuration';


-- ============================================================
-- TABLE: dashboards
-- Dashboard containers that hold multiple charts
-- ============================================================
CREATE TABLE `dashboards` (
    `id`               INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    `workspace_id`     INT UNSIGNED  NOT NULL,
    `name`             VARCHAR(200)  NOT NULL,
    `description`      TEXT          NULL DEFAULT NULL,
    `slug`             VARCHAR(220)  NOT NULL,
    `layout_config`    JSON          NULL DEFAULT NULL COMMENT 'Grid layout positions for all widgets',
    `filters_config`   JSON          NULL DEFAULT NULL COMMENT 'Global filter widget definitions',
    `refresh_interval` INT UNSIGNED  NOT NULL DEFAULT 0 COMMENT 'Dashboard-level refresh (0 = manual)',
    `theme`            ENUM('light','dark','system') NOT NULL DEFAULT 'system',
    `is_public`        TINYINT(1)    NOT NULL DEFAULT 0,
    `public_token`     VARCHAR(100)  NULL DEFAULT NULL UNIQUE,
    `public_password`  VARCHAR(255)  NULL DEFAULT NULL COMMENT 'Optional bcrypt hashed password for public link',
    `public_expires_at` DATETIME     NULL DEFAULT NULL,
    `is_pinned`        TINYINT(1)    NOT NULL DEFAULT 0 COMMENT 'Pinned dashboards appear first',
    `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 DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_dashboard_slug` (`workspace_id`, `slug`),
    INDEX `idx_dashboards_workspace` (`workspace_id`),
    INDEX `idx_dashboards_public`    (`public_token`),
    CONSTRAINT `fk_dashboards_workspace`
        FOREIGN KEY (`workspace_id`) REFERENCES `workspaces` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_dashboards_created_by`
        FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Dashboard containers';


-- ============================================================
-- TABLE: dashboard_widgets
-- Each widget on a dashboard (chart, text, image, filter)
-- ============================================================
CREATE TABLE `dashboard_widgets` (
    `id`             INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    `dashboard_id`   INT UNSIGNED  NOT NULL,
    `chart_id`       INT UNSIGNED  NULL DEFAULT NULL COMMENT 'NULL for non-chart widgets',
    `widget_type`    ENUM('chart','text','image','filter_date','filter_dropdown') NOT NULL DEFAULT 'chart',
    `title`          VARCHAR(200)  NULL DEFAULT NULL COMMENT 'Widget-level title override',
    -- Grid position (react-grid-layout style)
    `grid_x`         TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `grid_y`         TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `grid_w`         TINYINT UNSIGNED NOT NULL DEFAULT 2 COMMENT 'Width in grid units',
    `grid_h`         TINYINT UNSIGNED NOT NULL DEFAULT 2 COMMENT 'Height in grid units',
    -- Content for non-chart widgets
    `content`        TEXT          NULL DEFAULT NULL COMMENT 'Markdown for text widgets, URL for image widgets',
    `widget_config`  JSON          NULL DEFAULT NULL COMMENT 'Extra config (filter field bindings, image fit, etc.)',
    `sort_order`     INT UNSIGNED  NOT NULL DEFAULT 0,
    `created_at`     DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`     DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_dw_dashboard` (`dashboard_id`),
    INDEX `idx_dw_chart`     (`chart_id`),
    CONSTRAINT `fk_dw_dashboard`
        FOREIGN KEY (`dashboard_id`) REFERENCES `dashboards` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_dw_chart`
        FOREIGN KEY (`chart_id`) REFERENCES `charts` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Widgets placed on a dashboard';


-- ============================================================
-- TABLE: alerts
-- Threshold-based alert definitions on chart metrics
-- ============================================================
CREATE TABLE `alerts` (
    `id`              INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    `workspace_id`    INT UNSIGNED  NOT NULL,
    `chart_id`        INT UNSIGNED  NOT NULL,
    `name`            VARCHAR(200)  NOT NULL,
    `metric_field`    VARCHAR(150)  NOT NULL COMMENT 'Which data field to watch',
    `condition`       ENUM('gt','lt','eq','gte','lte') NOT NULL COMMENT 'greater than, less than, etc.',
    `threshold`       DECIMAL(20,4) NOT NULL,
    `check_interval`  SMALLINT UNSIGNED NOT NULL DEFAULT 15 COMMENT 'Check every N minutes',
    -- Notification
    `notify_email`    TINYINT(1)    NOT NULL DEFAULT 0,
    `email_addresses` TEXT          NULL DEFAULT NULL COMMENT 'Comma-separated list of emails',
    `notify_slack`    TINYINT(1)    NOT NULL DEFAULT 0,
    `slack_webhook`   VARCHAR(500)  NULL DEFAULT NULL,
    -- State
    `is_active`       TINYINT(1)    NOT NULL DEFAULT 1,
    `is_muted_until`  DATETIME      NULL DEFAULT NULL,
    `last_checked_at` DATETIME      NULL DEFAULT NULL,
    `last_triggered_at` DATETIME    NULL DEFAULT NULL,
    `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 DEFAULT NULL,
    PRIMARY KEY (`id`),
    INDEX `idx_alerts_workspace` (`workspace_id`),
    INDEX `idx_alerts_chart`     (`chart_id`),
    CONSTRAINT `fk_alerts_workspace`
        FOREIGN KEY (`workspace_id`) REFERENCES `workspaces` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_alerts_chart`
        FOREIGN KEY (`chart_id`) REFERENCES `charts` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_alerts_created_by`
        FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Alert rules defined on chart metrics';


-- ============================================================
-- TABLE: alert_history
-- Log of every time an alert was triggered
-- ============================================================
CREATE TABLE `alert_history` (
    `id`             INT UNSIGNED   NOT NULL AUTO_INCREMENT,
    `alert_id`       INT UNSIGNED   NOT NULL,
    `triggered_at`   DATETIME       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `metric_value`   DECIMAL(20,4)  NOT NULL COMMENT 'Value that triggered the alert',
    `channels_notified` JSON        NULL DEFAULT NULL COMMENT 'Which channels were notified',
    `status`         ENUM('sent','failed','muted') NOT NULL DEFAULT 'sent',
    `error_message`  TEXT           NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    INDEX `idx_ah_alert` (`alert_id`),
    CONSTRAINT `fk_ah_alert`
        FOREIGN KEY (`alert_id`) REFERENCES `alerts` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='History of triggered alert events';


-- ============================================================
-- TABLE: chart_exports
-- Log of chart/dashboard exports and downloads
-- ============================================================
CREATE TABLE `chart_exports` (
    `id`           INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    `workspace_id` INT UNSIGNED  NOT NULL,
    `chart_id`     INT UNSIGNED  NULL DEFAULT NULL,
    `dashboard_id` INT UNSIGNED  NULL DEFAULT NULL,
    `export_type`  ENUM('png','svg','csv','excel','pdf') NOT NULL,
    `file_path`    VARCHAR(500)  NULL DEFAULT NULL,
    `file_size`    INT UNSIGNED  NULL DEFAULT NULL COMMENT 'File size in bytes',
    `exported_by`  INT UNSIGNED  NOT NULL,
    `created_at`   DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_ce_workspace` (`workspace_id`),
    CONSTRAINT `fk_ce_workspace`
        FOREIGN KEY (`workspace_id`) REFERENCES `workspaces` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_ce_exported_by`
        FOREIGN KEY (`exported_by`) REFERENCES `users` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Log of chart and dashboard export requests';


-- ============================================================
-- TABLE: shared_links
-- Public share links for dashboards and charts
-- ============================================================
CREATE TABLE `shared_links` (
    `id`             INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    `token`          VARCHAR(100)  NOT NULL UNIQUE,
    `type`           ENUM('dashboard','chart') NOT NULL,
    `resource_id`    INT UNSIGNED  NOT NULL,
    `workspace_id`   INT UNSIGNED  NOT NULL,
    `password_hash`  VARCHAR(255)  NULL DEFAULT NULL,
    `expires_at`     DATETIME      NULL DEFAULT NULL,
    `view_count`     INT UNSIGNED  NOT NULL DEFAULT 0,
    `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,
    PRIMARY KEY (`id`),
    INDEX `idx_sl_token` (`token`),
    CONSTRAINT `fk_sl_workspace`
        FOREIGN KEY (`workspace_id`) REFERENCES `workspaces` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_sl_created_by`
        FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Public sharing links for dashboards and charts';


-- ============================================================
-- TABLE: audit_logs
-- Immutable record of significant user actions
-- ============================================================
CREATE TABLE `audit_logs` (
    `id`           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `workspace_id` INT UNSIGNED    NULL DEFAULT NULL COMMENT 'NULL for super-admin actions',
    `user_id`      INT UNSIGNED    NULL DEFAULT NULL COMMENT 'NULL for system actions',
    `action`       VARCHAR(100)    NOT NULL COMMENT 'e.g. dashboard.created, user.login',
    `resource_type` VARCHAR(50)    NULL DEFAULT NULL COMMENT 'e.g. dashboard, chart, data_source',
    `resource_id`  INT UNSIGNED    NULL DEFAULT NULL,
    `old_value`    JSON            NULL DEFAULT NULL COMMENT 'State before the action',
    `new_value`    JSON            NULL DEFAULT NULL COMMENT 'State after the action',
    `ip_address`   VARCHAR(45)     NULL DEFAULT NULL COMMENT 'IPv4 or IPv6',
    `user_agent`   VARCHAR(500)    NULL DEFAULT NULL,
    `created_at`   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_al_workspace`     (`workspace_id`),
    INDEX `idx_al_user`          (`user_id`),
    INDEX `idx_al_action`        (`action`),
    INDEX `idx_al_created_at`    (`created_at`),
    CONSTRAINT `fk_al_workspace`
        FOREIGN KEY (`workspace_id`) REFERENCES `workspaces` (`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_al_user`
        FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Immutable audit trail of all user actions';


-- ============================================================
-- TABLE: user_sessions
-- Tracks active login sessions (for multi-device management)
-- ============================================================
CREATE TABLE `user_sessions` (
    `id`           INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    `user_id`      INT UNSIGNED  NOT NULL,
    `session_token` VARCHAR(255) NOT NULL UNIQUE,
    `ip_address`   VARCHAR(45)   NULL DEFAULT NULL,
    `user_agent`   VARCHAR(500)  NULL DEFAULT NULL,
    `last_active`  DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `expires_at`   DATETIME      NOT NULL,
    `created_at`   DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_us_user`  (`user_id`),
    INDEX `idx_us_token` (`session_token`),
    CONSTRAINT `fk_us_user`
        FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Active user login sessions';


-- ============================================================
-- TABLE: tags
-- Tagging system for charts and dashboards
-- ============================================================
CREATE TABLE `tags` (
    `id`           INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    `workspace_id` INT UNSIGNED  NOT NULL,
    `name`         VARCHAR(80)   NOT NULL,
    `color`        VARCHAR(7)    NOT NULL DEFAULT '#6366f1' COMMENT 'Hex color code',
    `created_at`   DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_tag_name` (`workspace_id`, `name`),
    CONSTRAINT `fk_tags_workspace`
        FOREIGN KEY (`workspace_id`) REFERENCES `workspaces` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Tags for organizing charts and dashboards';


-- ============================================================
-- TABLE: taggables
-- Polymorphic tag assignments (chart or dashboard)
-- ============================================================
CREATE TABLE `taggables` (
    `tag_id`          INT UNSIGNED NOT NULL,
    `taggable_id`     INT UNSIGNED NOT NULL,
    `taggable_type`   ENUM('chart','dashboard') NOT NULL,
    PRIMARY KEY (`tag_id`, `taggable_id`, `taggable_type`),
    CONSTRAINT `fk_taggables_tag`
        FOREIGN KEY (`tag_id`) REFERENCES `tags` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Polymorphic join table for tagging';


-- ============================================================
-- TABLE: query_cache
-- Stores cached query results to reduce DB load
-- ============================================================
CREATE TABLE `query_cache` (
    `id`             INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    `cache_key`      VARCHAR(255)  NOT NULL UNIQUE COMMENT 'MD5 hash of datasource+query',
    `data_source_id` INT UNSIGNED  NOT NULL,
    `result_data`    LONGTEXT      NOT NULL COMMENT 'JSON encoded query result',
    `row_count`      INT UNSIGNED  NOT NULL DEFAULT 0,
    `expires_at`     DATETIME      NOT NULL,
    `created_at`     DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_qc_cache_key`  (`cache_key`),
    INDEX `idx_qc_expires_at` (`expires_at`),
    CONSTRAINT `fk_qc_datasource`
        FOREIGN KEY (`data_source_id`) REFERENCES `data_sources` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Cached query results with TTL';


-- ============================================================
-- TABLE: settings
-- Key-value application settings (per workspace or global)
-- ============================================================
CREATE TABLE `settings` (
    `id`           INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    `workspace_id` INT UNSIGNED  NULL DEFAULT NULL COMMENT 'NULL = global/system setting',
    `key`          VARCHAR(150)  NOT NULL,
    `value`        TEXT          NULL DEFAULT NULL,
    `type`         ENUM('string','integer','boolean','json') NOT NULL DEFAULT 'string',
    `created_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_setting` (`workspace_id`, `key`),
    CONSTRAINT `fk_settings_workspace`
        FOREIGN KEY (`workspace_id`) REFERENCES `workspaces` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Key-value settings store';


-- ============================================================
-- INITIAL SEED DATA
-- ============================================================

-- Default Super Admin
INSERT INTO `users` (`name`, `email`, `password`, `role`, `email_verified`, `is_active`)
VALUES (
    'Super Admin',
    'admin@chartboard.local',
    '$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', -- Admin@1234
    'superadmin',
    1,
    1
);

-- Default Workspace
INSERT INTO `workspaces` (`name`, `slug`, `description`, `owner_id`)
VALUES ('Default Workspace', 'default-workspace', 'The default Chart-Board workspace', 1);

-- Add super admin as workspace admin
INSERT INTO `workspace_members` (`workspace_id`, `user_id`, `role`, `joined_at`)
VALUES (1, 1, 'admin', NOW());

-- Global Settings
INSERT INTO `settings` (`workspace_id`, `key`, `value`, `type`) VALUES
(NULL, 'app_name',            'Chart-Board',   'string'),
(NULL, 'app_version',         '1.0.0',         'string'),
(NULL, 'allow_registration',  '1',             'boolean'),
(NULL, 'max_workspaces',      '10',            'integer'),
(NULL, 'default_theme',       'light',         'string'),
(NULL, 'smtp_configured',     '0',             'boolean');


SET FOREIGN_KEY_CHECKS = 1;

-- ============================================================
-- End of chart-board schema
-- ============================================================