-- ════════════════════════════════════════════════════════════════════════════
--  SUPRIXX DATABASE UPGRADE  (companies · fiscal years · access · letterheads)
--  Run once in cPanel → phpMyAdmin → select your offers database → SQL → Go.
--  Safe to run again: it only adds what is missing and never deletes anything.
--  Works on MySQL 5.7+/8 and MariaDB 10.2+.
--  TAKE A BACKUP FIRST: phpMyAdmin → Export → Go.
-- ════════════════════════════════════════════════════════════════════════════

-- ─── 1. NEW TABLES ──────────────────────────────────────────────────────────

-- Companies offers are made for (each with its own letterhead + numbering)
CREATE TABLE IF NOT EXISTS companies (
    id                INT AUTO_INCREMENT PRIMARY KEY,
    name              VARCHAR(150) NOT NULL,              -- short name, e.g. Nepal Ion Exchange
    legal_name        VARCHAR(200) NOT NULL,              -- PDF footer, e.g. NEPAL ION EXCHANGE PRIVATE LIMITED
    header_name       VARCHAR(200) NOT NULL,              -- big name on the letterhead
    sign_for          VARCHAR(200) NOT NULL,              -- "For, <this>"
    tagline           VARCHAR(200) NULL,
    logo_path         VARCHAR(255) NULL,                  -- e.g. assets/nie_header.png
    head_office       VARCHAR(255) NULL,
    branch_offices    TEXT NULL,                          -- one per line
    reg_no            VARCHAR(100) NULL,
    email             VARCHAR(150) NULL,
    phone             VARCHAR(100) NULL,
    website           VARCHAR(150) NULL,
    ref_locations     TEXT NULL,                          -- "NP-BRJ|Birgunj" per line
    default_ref_loc   VARCHAR(20) NULL,
    default_initials  VARCHAR(10) NULL,
    price_term        VARCHAR(200) NULL,
    letterhead_mode   VARCHAR(10) NOT NULL DEFAULT 'builtin',  -- builtin = standard design, page = company's own letterhead
    letterhead_header VARCHAR(255) NULL,                  -- top strip of the letterhead (on-screen view)
    letterhead_footer VARCHAR(255) NULL,                  -- bottom strip of the letterhead (on-screen view)
    letterhead_page   VARCHAR(255) NULL,                  -- the whole letterhead page (printed behind every PDF page)
    lh_top            DECIMAL(6,2) NULL,                  -- writing area starts at this % of the page height
    lh_bottom         DECIMAL(6,2) NULL,                  -- ... and stops this % above the bottom
    is_active         TINYINT(1) NOT NULL DEFAULT 1,
    created_at        TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) DEFAULT CHARSET=utf8mb4;

-- Fiscal years (e.g. 2083-84); one is "current"
CREATE TABLE IF NOT EXISTS fiscal_years (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    label       VARCHAR(20) NOT NULL UNIQUE,
    start_date  DATE NULL,
    end_date    DATE NULL,
    is_current  TINYINT(1) NOT NULL DEFAULT 0,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) DEFAULT CHARSET=utf8mb4;

-- Which companies each (non-admin) user can see; admins see all
CREATE TABLE IF NOT EXISTS user_companies (
    user_id     INT NOT NULL,
    company_id  INT NOT NULL,
    PRIMARY KEY (user_id, company_id)
) DEFAULT CHARSET=utf8mb4;

-- Offer numbers: separate per company per fiscal year (restart at 1)
CREATE TABLE IF NOT EXISTS sx_offer_counters (
    company_id  INT NOT NULL,
    fy_id       INT NOT NULL,
    value       INT NOT NULL DEFAULT 0,
    PRIMARY KEY (company_id, fy_id)
) DEFAULT CHARSET=utf8mb4;

-- Upgrade bookkeeping (lets the website know this upgrade is done)
CREATE TABLE IF NOT EXISTS sx_meta (
    k  VARCHAR(50) PRIMARY KEY,
    v  VARCHAR(255) NOT NULL
) DEFAULT CHARSET=utf8mb4;


-- ─── 2. NEW COLUMNS (added only if missing) ─────────────────────────────────
-- offers.company_id / offers.fy_id / index
SET @s = (SELECT IF(COUNT(*) = 0, 'ALTER TABLE offers ADD COLUMN company_id INT NULL', 'SELECT 1')
          FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'offers' AND COLUMN_NAME = 'company_id');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

SET @s = (SELECT IF(COUNT(*) = 0, 'ALTER TABLE offers ADD COLUMN fy_id INT NULL', 'SELECT 1')
          FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'offers' AND COLUMN_NAME = 'fy_id');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

SET @s = (SELECT IF(COUNT(*) = 0, 'ALTER TABLE offers ADD INDEX idx_company_fy (company_id, fy_id)', 'SELECT 1')
          FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'offers' AND INDEX_NAME = 'idx_company_fy');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

-- users.default_company_id (company the user opens in)
SET @s = (SELECT IF(COUNT(*) = 0, 'ALTER TABLE users ADD COLUMN default_company_id INT NULL', 'SELECT 1')
          FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users' AND COLUMN_NAME = 'default_company_id');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

-- users: mobile-app sign-in + lockout columns (from earlier updates, if missing)
SET @s = (SELECT IF(COUNT(*) = 0, 'ALTER TABLE users ADD COLUMN app_token VARCHAR(64) NULL, ADD COLUMN app_token_expires DATETIME NULL', 'SELECT 1')
          FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users' AND COLUMN_NAME = 'app_token');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

SET @s = (SELECT IF(COUNT(*) = 0, 'ALTER TABLE users ADD COLUMN failed_attempts INT NOT NULL DEFAULT 0', 'SELECT 1')
          FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users' AND COLUMN_NAME = 'failed_attempts');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

SET @s = (SELECT IF(COUNT(*) = 0, 'ALTER TABLE users ADD COLUMN locked_until DATETIME NULL', 'SELECT 1')
          FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users' AND COLUMN_NAME = 'locked_until');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

SET @s = (SELECT IF(COUNT(*) = 0, 'ALTER TABLE users ADD COLUMN last_login DATETIME NULL', 'SELECT 1')
          FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users' AND COLUMN_NAME = 'last_login');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

-- companies: letterhead columns (for a companies table made by an earlier version)
SET @s = (SELECT IF(COUNT(*) = 0, 'ALTER TABLE companies ADD COLUMN letterhead_mode VARCHAR(10) NOT NULL DEFAULT ''builtin'', ADD COLUMN letterhead_header VARCHAR(255) NULL, ADD COLUMN letterhead_footer VARCHAR(255) NULL', 'SELECT 1')
          FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'companies' AND COLUMN_NAME = 'letterhead_mode');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

-- companies: whole-page letterhead columns (version 4.1)
SET @s = (SELECT IF(COUNT(*) = 0, 'ALTER TABLE companies ADD COLUMN letterhead_page VARCHAR(255) NULL', 'SELECT 1')
          FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'companies' AND COLUMN_NAME = 'letterhead_page');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;
SET @s = (SELECT IF(COUNT(*) = 0, 'ALTER TABLE companies ADD COLUMN lh_top DECIMAL(6,2) NULL', 'SELECT 1')
          FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'companies' AND COLUMN_NAME = 'lh_top');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;
SET @s = (SELECT IF(COUNT(*) = 0, 'ALTER TABLE companies ADD COLUMN lh_bottom DECIMAL(6,2) NULL', 'SELECT 1')
          FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'companies' AND COLUMN_NAME = 'lh_bottom');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;


-- users.is_super: only a Super Admin manages users, companies and fiscal years (version 4.2)
SET @s = (SELECT IF(COUNT(*) = 0, 'ALTER TABLE users ADD COLUMN is_super TINYINT(1) NOT NULL DEFAULT 0', 'SELECT 1')
          FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users' AND COLUMN_NAME = 'is_super');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;
-- the built-in "admin" account becomes Super Admin (or the oldest admin), only if there is none yet
UPDATE users u
JOIN (SELECT id FROM users WHERE role = 'admin' ORDER BY (username = 'admin') DESC, id LIMIT 1) pick ON pick.id = u.id
SET u.is_super = 1
WHERE NOT EXISTS (SELECT 1 FROM (SELECT id FROM users WHERE is_super = 1) already);

-- fiscal-year calendars (version 4.4): Nepali B.S. (2083-84) or Indian A.D. (2026-27)
SET @s = (SELECT IF(COUNT(*) = 0, 'ALTER TABLE fiscal_years ADD COLUMN calendar VARCHAR(2) NOT NULL DEFAULT ''BS''', 'SELECT 1')
          FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fiscal_years' AND COLUMN_NAME = 'calendar');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;
SET @s = (SELECT IF(COUNT(*) = 0, 'ALTER TABLE companies ADD COLUMN fy_calendar VARCHAR(2) NOT NULL DEFAULT ''BS''', 'SELECT 1')
          FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'companies' AND COLUMN_NAME = 'fy_calendar');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

-- currency + tax per company (version 4.6); empty = defaults of the company's calendar
SET @s = (SELECT IF(COUNT(*) = 0, 'ALTER TABLE companies ADD COLUMN currency VARCHAR(12) NULL', 'SELECT 1')
          FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'companies' AND COLUMN_NAME = 'currency');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;
SET @s = (SELECT IF(COUNT(*) = 0, 'ALTER TABLE companies ADD COLUMN tax_name VARCHAR(20) NULL', 'SELECT 1')
          FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'companies' AND COLUMN_NAME = 'tax_name');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;
SET @s = (SELECT IF(COUNT(*) = 0, 'ALTER TABLE companies ADD COLUMN tax_rate DECIMAL(5,2) NULL', 'SELECT 1')
          FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'companies' AND COLUMN_NAME = 'tax_rate');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

-- ─── 3. YOUR EXISTING DATA ──────────────────────────────────────────────────

-- 3a. Nepal Ion Exchange = company #1 (only if there are no companies yet)
INSERT INTO companies (name, legal_name, header_name, sign_for, tagline, logo_path, head_office, branch_offices,
                       reg_no, email, phone, website, ref_locations, default_ref_loc, default_initials, price_term)
SELECT 'Nepal Ion Exchange', 'NEPAL ION EXCHANGE PRIVATE LIMITED', 'NEPAL ION EXCHANGE PVT. LTD',
       'Nepal Ion Exchange Pvt. Ltd.', 'Water Treatment & Process Solutions', 'assets/nie_header.png',
       'Birgunj, Nepal', CONCAT('Biratnagar, Nepal', CHAR(10), 'Butwal, Nepal'), '256501/077/78',
       'nepalionexchange@gmail.com', '9855035809', 'www.nepalionexchange.com',
       CONCAT('NP-BRJ|Birgunj', CHAR(10), 'NP-BRT|Biratnagar', CHAR(10), 'NP-BTW|Butwal', CHAR(10), 'NP-KTM|Kathmandu'),
       'NP-BRJ', 'SB', 'Ex- Birgunj Go-down Basis'
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM companies);

SET @nie = (SELECT MIN(id) FROM companies);

-- 3b. Fiscal years: every year used in existing offers + the current one (2083-84)
INSERT IGNORE INTO fiscal_years (label)
SELECT DISTINCT JSON_UNQUOTE(JSON_EXTRACT(data, '$.ref_year'))
FROM offers
WHERE JSON_UNQUOTE(JSON_EXTRACT(data, '$.ref_year')) REGEXP '^[0-9]{4}-[0-9]{2}$';

INSERT IGNORE INTO fiscal_years (label) VALUES ('2083-84');

-- make 2083-84 current (only if no year is current yet)
SET @has_current = (SELECT COUNT(*) FROM fiscal_years WHERE is_current = 1);
UPDATE fiscal_years SET is_current = (label = '2083-84') WHERE @has_current = 0;

SET @cur_fy = (SELECT id FROM fiscal_years WHERE is_current = 1 ORDER BY id LIMIT 1);

-- 3c. Every existing offer -> Nepal Ion Exchange + the fiscal year in its reference
UPDATE offers SET company_id = @nie WHERE company_id IS NULL;

UPDATE offers o
JOIN fiscal_years f ON f.label = JSON_UNQUOTE(JSON_EXTRACT(o.data, '$.ref_year'))
SET o.fy_id = f.id
WHERE o.fy_id IS NULL;

UPDATE offers SET fy_id = @cur_fy WHERE fy_id IS NULL;   -- offers without a year -> current year

-- 3d. Offer numbering continues from the highest number used per company + year
INSERT IGNORE INTO sx_offer_counters (company_id, fy_id, value)
SELECT company_id, fy_id, 0 FROM offers
WHERE company_id IS NOT NULL AND fy_id IS NOT NULL
GROUP BY company_id, fy_id;

UPDATE sx_offer_counters c
JOIN (SELECT company_id, fy_id, COALESCE(MAX(ref_num), 0) AS m
      FROM offers WHERE company_id IS NOT NULL AND fy_id IS NOT NULL
      GROUP BY company_id, fy_id) x
  ON x.company_id = c.company_id AND x.fy_id = c.fy_id
SET c.value = GREATEST(c.value, x.m);

-- 3e. Existing users keep working: access to Nepal Ion Exchange
--     (first run only -- never re-adds access an admin removed later)
INSERT IGNORE INTO user_companies (user_id, company_id)
SELECT u.id, @nie FROM users u
WHERE NOT EXISTS (SELECT 1 FROM sx_meta WHERE k = 'schema_version');

UPDATE users SET default_company_id = @nie WHERE default_company_id IS NULL;


-- ─── 4. MARK THE UPGRADE AS DONE ────────────────────────────────────────────
-- (the website then skips its own automatic upgrade)
-- years below 2070 can only be Indian (A.D.) years
UPDATE fiscal_years SET calendar = IF(CAST(LEFT(label, 4) AS UNSIGNED) < 2070, 'AD', 'BS');
REPLACE INTO sx_meta (k, v) VALUES ('schema_version', '6');


-- ─── 5. CHECK (shows the result) ────────────────────────────────────────────
SELECT 'companies' AS what, COUNT(*) AS rows_count FROM companies
UNION ALL SELECT 'fiscal years', COUNT(*) FROM fiscal_years
UNION ALL SELECT 'offers without company', COUNT(*) FROM offers WHERE company_id IS NULL
UNION ALL SELECT 'offers without fiscal year', COUNT(*) FROM offers WHERE fy_id IS NULL
UNION ALL SELECT 'user access rows', COUNT(*) FROM user_companies;