nse_query_builder_sql.sql

-- ============================================
-- CREATE SCHEMA
-- ============================================
CREATE SCHEMA IF NOT EXISTS nse_query_builder;
USE nse_query_builder;

-- ============================================
-- DROP TABLES (OPTIONAL FOR CLEAN RESET)
-- ============================================
DROP TABLE IF EXISTS query_queue;
DROP TABLE IF EXISTS saved_queries;
DROP TABLE IF EXISTS dwh_table_data;
DROP TABLE IF EXISTS dwh_rbac_users;

-- ============================================
-- TABLE: saved_queries
-- ============================================
CREATE TABLE saved_queries (
    id INT AUTO_INCREMENT PRIMARY KEY,
    query_name VARCHAR(100),
    sql_text TEXT,
    canvas_json JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_by INT,
    query_status VARCHAR(100)
);

-- ============================================
-- TABLE: dwh_table_data
-- ============================================
CREATE TABLE dwh_table_data (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    columns JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    access_codes VARCHAR(100)
);

-- ============================================
-- TABLE: dwh_rbac_users
-- ============================================
CREATE TABLE dwh_rbac_users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    emp_id VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    access_id VARCHAR(100),
    hashed_password VARCHAR(100)
);

-- ============================================
-- TABLE: query_queue
-- FIXED FOREIGN KEY SYNTAX
-- ============================================
CREATE TABLE query_queue (
    id INT AUTO_INCREMENT PRIMARY KEY,
    query_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(50) DEFAULT 'PENDING',
    FOREIGN KEY (query_id) REFERENCES saved_queries(id) ON DELETE CASCADE
);

-- ============================================
-- INSERT SAMPLE USERS
-- ============================================
INSERT INTO dwh_rbac_users (name, emp_id, access_id, hashed_password)
VALUES
('Admin User', 'EMP1001', 'ADMIN', 'hashed_password_admin'),
('Analyst User', 'EMP2001', 'ANALYST', 'hashed_password_analyst'),
('Viewer User', 'EMP3001', 'VIEWER', 'hashed_password_viewer');

-- ============================================
-- INSERT SAMPLE TABLE METADATA (dwh_table_data)
-- columns JSON format:
-- { "columns": [ { "name": "...", "type": "..."} ] }
-- ============================================

INSERT INTO dwh_table_data (name, columns, access_codes)
VALUES
(
  'orders_buy_sell',
  JSON_OBJECT(
    'columns', JSON_ARRAY(
      JSON_OBJECT('name','customer_id','type','INT'),
      JSON_OBJECT('name','customer_name','type','VARCHAR(255)'),
      JSON_OBJECT('name','email','type','VARCHAR(255)'),
      JSON_OBJECT('name','phone','type','VARCHAR(50)'),
      JSON_OBJECT('name','city','type','VARCHAR(100)'),
      JSON_OBJECT('name','created_date','type','DATE')
    )
  ),
  'ADMIN,ANALYST'
),
(
  'orders_trd_log',
  JSON_OBJECT(
    'columns', JSON_ARRAY(
      JSON_OBJECT('name','order_id','type','INT'),
      JSON_OBJECT('name','customer_id','type','INT'),
      JSON_OBJECT('name','order_date','type','DATE'),
      JSON_OBJECT('name','order_status','type','VARCHAR(100)'),
      JSON_OBJECT('name','total_amount','type','DECIMAL(18,2)')
    )
  ),
  'ADMIN,ANALYST'
),
(
  'i2_tradelog',
  JSON_OBJECT(
    'columns', JSON_ARRAY(
      JSON_OBJECT('name','order_item_id','type','INT'),
      JSON_OBJECT('name','order_id','type','INT'),
      JSON_OBJECT('name','product_id','type','INT'),
      JSON_OBJECT('name','quantity','type','INT'),
      JSON_OBJECT('name','unit_price','type','DECIMAL(18,2)')
    )
  ),
  'ADMIN,ANALYST'
),
(
  'fao_buy_sell',
  JSON_OBJECT(
    'columns', JSON_ARRAY(
      JSON_OBJECT('name','product_id','type','INT'),
      JSON_OBJECT('name','product_name','type','VARCHAR(255)'),
      JSON_OBJECT('name','category','type','VARCHAR(100)'),
      JSON_OBJECT('name','brand','type','VARCHAR(100)'),
      JSON_OBJECT('name','price','type','DECIMAL(18,2)')
    )
  ),
  'ADMIN,ANALYST,VIEWER'
),
(
  'fao_tradelog',
  JSON_OBJECT(
    'columns', JSON_ARRAY(
      JSON_OBJECT('name','payment_id','type','INT'),
      JSON_OBJECT('name','order_id','type','INT'),
      JSON_OBJECT('name','payment_date','type','DATE'),
      JSON_OBJECT('name','payment_method','type','VARCHAR(100)'),
      JSON_OBJECT('name','amount','type','DECIMAL(18,2)')
    )
  ),
  'ADMIN,ANALYST'
),
(
  'new_ords_tbl',
  JSON_OBJECT(
    'columns', JSON_ARRAY(
      JSON_OBJECT('name','emp_id','type','INT'),
      JSON_OBJECT('name','emp_name','type','VARCHAR(255)'),
      JSON_OBJECT('name','department','type','VARCHAR(100)'),
      JSON_OBJECT('name','salary','type','DECIMAL(18,2)'),
      JSON_OBJECT('name','join_date','type','DATE')
    )
  ),
  'ADMIN'
),
(
  'test_tables',
  JSON_OBJECT(
    'columns', JSON_ARRAY(
      JSON_OBJECT('name','branch_id','type','INT'),
      JSON_OBJECT('name','branch_name','type','VARCHAR(255)'),
      JSON_OBJECT('name','city','type','VARCHAR(100)')
    )
  ),
  'ADMIN,ANALYST'
);

-- ============================================
-- INSERT SAMPLE SAVED QUERY
-- ============================================

INSERT INTO saved_queries (query_name, sql_text, canvas_json, created_by, query_status)
VALUES
(
  'Customer Orders Summary',
  'SELECT c.customer_name, o.order_id, o.total_amount FROM customers c INNER JOIN orders o ON c.customer_id = o.customer_id;',
  JSON_OBJECT(
    'nodes', JSON_ARRAY(
      JSON_OBJECT(
        'id','customers_1',
        'type','tableNode',
        'position', JSON_OBJECT('x', 100, 'y', 100),
        'data', JSON_OBJECT(
          'tableName','customers',
          'alias','c',
          'columns', JSON_ARRAY('customer_id','customer_name','email','phone','city','created_date')
        )
      ),
      JSON_OBJECT(
        'id','orders_1',
        'type','tableNode',
        'position', JSON_OBJECT('x', 450, 'y', 100),
        'data', JSON_OBJECT(
          'tableName','orders',
          'alias','o',
          'columns', JSON_ARRAY('order_id','customer_id','order_date','order_status','total_amount')
        )
      )
    ),
    'edges', JSON_ARRAY(
      JSON_OBJECT(
        'id','e1',
        'source','customers_1',
        'target','orders_1',
        'type','joinEdge',
        'data', JSON_OBJECT(
          'joinType','INNER',
          'leftColumn','customer_id',
          'rightColumn','customer_id'
        )
      )
    ),
    'selectedColumns', JSON_ARRAY(
      JSON_OBJECT('tableAlias','c','column','customer_name'),
      JSON_OBJECT('tableAlias','o','column','order_id'),
      JSON_OBJECT('tableAlias','o','column','total_amount')
    )
  ),
  1,
  'SAVED'
);

-- ============================================
-- INSERT SAMPLE QUERY QUEUE ENTRY
-- ============================================

INSERT INTO query_queue (query_id, status)
VALUES
(1, 'PENDING');