-- ============================================================
-- FILO HEVIS OMS — DATABASE SETUP
-- Run this in phpPgAdmin → SQL tab on your DB
-- ============================================================

-- Create agent_users table for login
CREATE TABLE IF NOT EXISTS agent_users (
    id            SERIAL PRIMARY KEY,
    username      VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    name          VARCHAR(100) NOT NULL,
    role          VARCHAR(50)  NOT NULL DEFAULT 'agent',
    -- Roles: admin / team_lead / dispatch / agent
    is_active     BOOLEAN NOT NULL DEFAULT true,
    created_at    TIMESTAMP DEFAULT NOW()
);

-- ============================================================
-- INSERT AGENTS
-- IMPORTANT: Generate password hashes using this PHP snippet:
--   php -r "echo password_hash('YourPassword123', PASSWORD_DEFAULT);"
-- Run it in terminal or use https://phpassword.tools
-- Replace the $2y$... strings below with your generated hashes
-- ============================================================

INSERT INTO agent_users (username, password_hash, name, role) VALUES
('atul',     '$2y$12$REPLACE_WITH_HASH_FOR_ATUL',    'Atul',     'admin'),
('nishant',  '$2y$12$REPLACE_WITH_HASH_FOR_NISHANT', 'Nishant Sir', 'admin'),
('shalu',    '$2y$12$REPLACE_WITH_HASH_FOR_SHALU',   'Shalu',    'team_lead'),
('harika',   '$2y$12$REPLACE_WITH_HASH_FOR_HARIKA',  'Harika',   'agent'),
('shweta',   '$2y$12$REPLACE_WITH_HASH_FOR_SHWETA',  'Shweta',   'agent'),
('deepali',  '$2y$12$REPLACE_WITH_HASH_FOR_DEEPALI', 'Deepali',  'agent'),
('geeta',    '$2y$12$REPLACE_WITH_HASH_FOR_GEETA',   'Geeta',    'agent'),
('yamini',   '$2y$12$REPLACE_WITH_HASH_FOR_YAMINI',  'Yamini',   'agent'),
('hema',     '$2y$12$REPLACE_WITH_HASH_FOR_HEMA',    'Hema',     'agent'),
('dispatch', '$2y$12$REPLACE_WITH_HASH_FOR_DISPATCH','Dispatch Team', 'dispatch')
ON CONFLICT (username) DO NOTHING;

-- ============================================================
-- GENERATE ALL HASHES AT ONCE using this PHP script:
-- Save as generate_hashes.php and run: php generate_hashes.php
-- ============================================================
-- <?php
-- $users = [
--   'atul'     => 'Atul@2024',
--   'nishant'  => 'Nishant@2024',
--   'shalu'    => 'Shalu@2024',
--   'harika'   => 'Harika@2024',
--   'shweta'   => 'Shweta@2024',
--   'deepali'  => 'Deepali@2024',
--   'geeta'    => 'Geeta@2024',
--   'yamini'   => 'Yamini@2024',
--   'hema'     => 'Hema@2024',
--   'dispatch' => 'Dispatch@2024',
-- ];
-- foreach ($users as $u => $p) {
--   echo "$u: " . password_hash($p, PASSWORD_DEFAULT) . "\n";
-- }

-- ============================================================
-- Ensure order_status has the right columns
-- (your existing table should already have these)
-- ============================================================
ALTER TABLE order_status ADD COLUMN IF NOT EXISTS remark TEXT DEFAULT '';
ALTER TABLE order_status ADD COLUMN IF NOT EXISTS assign_timestamp TIMESTAMP;
ALTER TABLE order_status ADD COLUMN IF NOT EXISTS timestamp TIMESTAMP;

-- Add unique constraint on order_id if not already there (needed for UPSERT)
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint
        WHERE conname = 'order_status_order_id_key'
    ) THEN
        ALTER TABLE order_status ADD CONSTRAINT order_status_order_id_key UNIQUE (order_id);
    END IF;
END $$;

-- Add unique constraint on scanning.order_id for UPSERT
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint
        WHERE conname = 'scanning_order_id_key'
    ) THEN
        ALTER TABLE scanning ADD CONSTRAINT scanning_order_id_key UNIQUE (order_id);
    END IF;
END $$;

-- ============================================================
-- VERIFY: Check your tables look right
-- ============================================================
-- SELECT * FROM agent_users;
-- SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'order_status';
-- SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'scanning';
