CREATE DATABASE IF NOT EXISTS rokad_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE rokad_db;

-- Financial Years Table (Indian FY: April 1 to March 31)
CREATE TABLE IF NOT EXISTS financial_years (
    id INT AUTO_INCREMENT PRIMARY KEY,
    fy_name VARCHAR(20) NOT NULL UNIQUE, -- e.g., '2025-2026', '2026-2027'
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    is_active TINYINT(1) DEFAULT 1
) ENGINE=InnoDB;

-- Users Table (Login via Mobile Number)
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    mobile VARCHAR(15) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    salt VARCHAR(64) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Parties Table (Clients & Employees)
CREATE TABLE IF NOT EXISTS parties (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    mobile VARCHAR(15) NULL,
    type ENUM('Client', 'Employee') NOT NULL,
    status ENUM('Active', 'Inactive') DEFAULT 'Active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Transactions Table
CREATE TABLE IF NOT EXISTS transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    fy_id INT NOT NULL,
    party_id INT NOT NULL,
    txn_kind ENUM('DR', 'CR') NOT NULL,
    amount DECIMAL(12, 2) NOT NULL,
    description TEXT NULL,
    has_invoice TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (fy_id) REFERENCES financial_years(id) ON DELETE CASCADE,
    FOREIGN KEY (party_id) REFERENCES parties(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Transaction Invoice Line Items
CREATE TABLE IF NOT EXISTS transaction_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    transaction_id INT NOT NULL,
    item_name VARCHAR(150) NOT NULL,
    item_type VARCHAR(50) NULL,
    quantity DECIMAL(10, 2) NOT NULL DEFAULT 1,
    unit_price DECIMAL(12, 2) NOT NULL DEFAULT 0.00,
    total_price DECIMAL(12, 2) NOT NULL DEFAULT 0.00,
    FOREIGN KEY (transaction_id) REFERENCES transactions(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Seed Default Financial Years
INSERT INTO financial_years (fy_name, start_date, end_date, is_active) VALUES
('2025-2026', '2025-04-01', '2026-03-31', 1),
('2026-2027', '2026-04-01', '2027-03-31', 1)
ON DUPLICATE KEY UPDATE fy_name=fy_name;

-- Seed Default Admin User (Mobile: 9876543210, Password: Admin@12345)
-- Custom HMAC Salt + Hash implementation
INSERT INTO users (name, mobile, password_hash, salt) VALUES
('Admin User', '9876543210', 'a437f1c42f01fbd8f1610e7dd92f44c4b6935d259c76673551528cb617cf931b', 'rokad_static_salt_9876')
ON DUPLICATE KEY UPDATE id=id;

-- Seed Sample Parties
INSERT INTO parties (name, mobile, type, status) VALUES 
('Acme Corp', '9898011223', 'Client', 'Active'),
('TechCorp Inc', '9898022334', 'Client', 'Active'),
('Global Logistics', '9898033445', 'Client', 'Inactive'),
('John Doe', '9712011223', 'Employee', 'Active'),
('Jane Smith', '9712022334', 'Employee', 'Active');