-- =================================================================
-- DualCore ERP - Migration 002
-- Adds: user accounts, Guide/Language preferences, Feedback system,
-- multi-language support, and offline/online sync bookkeeping.
-- Run this AFTER schema.sql (which created accounts/items/vouchers/
-- voucher_entries/boms/production_orders).
-- =================================================================

USE acc_manu_db;

-- ---------------------------------------------------------------
-- Users & roles (DualCore ERP had no auth layer yet - added here
-- since Feedback, Guide preference, and Language preference all
-- need a logged-in user to attach to).
-- ---------------------------------------------------------------
CREATE TABLE IF NOT EXISTS users (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name          VARCHAR(150) NOT NULL,
    email         VARCHAR(150) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    role          ENUM('Super Admin','Accountant','Production Manager','Viewer') NOT NULL DEFAULT 'Viewer',
    is_active     TINYINT(1) NOT NULL DEFAULT 1,
    created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- NOTE: No Super Admin is seeded here with a hardcoded password hash.
-- Run setup/create_super_admin.php once after deploying (see that file)
-- to create your first Super Admin using PHP's own password_hash() -
-- generating a bcrypt hash by hand in SQL is error-prone and insecure.

-- ---------------------------------------------------------------
-- Per-user preferences: Guide ON/OFF (default ON) + language
-- ---------------------------------------------------------------
CREATE TABLE IF NOT EXISTS user_preferences (
    user_id       INT UNSIGNED PRIMARY KEY,
    guide_enabled TINYINT(1) NOT NULL DEFAULT 1,
    language_code VARCHAR(10) NOT NULL DEFAULT 'en',
    updated_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_pref_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS supported_languages (
    code          VARCHAR(10) PRIMARY KEY,
    name_english  VARCHAR(50) NOT NULL,
    name_native   VARCHAR(50) NOT NULL,
    is_active     TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB;

INSERT INTO supported_languages (code, name_english, name_native) VALUES
('en', 'English', 'English'),
('hi', 'Hindi', 'हिन्दी'),
('mr', 'Marathi', 'मराठी'),
('gu', 'Gujarati', 'ગુજરાતી'),
('pa', 'Punjabi', 'ਪੰਜਾਬੀ'),
('te', 'Telugu', 'తెలుగు'),
('ta', 'Tamil', 'தமிழ்'),
('bn', 'Bengali', 'বাংলা'),
('kn', 'Kannada', 'ಕನ್ನಡ'),
('es', 'Spanish', 'Español'),
('ar', 'Arabic', 'العربية')
ON DUPLICATE KEY UPDATE name_english = VALUES(name_english);

-- ---------------------------------------------------------------
-- Feedback / Complaint / Review
-- ---------------------------------------------------------------
CREATE TABLE IF NOT EXISTS feedback (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id    INT UNSIGNED NOT NULL,
    type       ENUM('Suggestion','Complaint','Review') NOT NULL,
    message    TEXT NOT NULL,
    status     ENUM('Pending','In Progress','Resolved','Closed') NOT NULL DEFAULT 'Pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_feedback_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS feedback_replies (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    feedback_id   INT UNSIGNED NOT NULL,
    admin_id      INT UNSIGNED NOT NULL,
    reply_message TEXT NOT NULL,
    created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_reply_feedback FOREIGN KEY (feedback_id) REFERENCES feedback(id) ON DELETE CASCADE,
    CONSTRAINT fk_reply_admin FOREIGN KEY (admin_id) REFERENCES users(id) ON DELETE RESTRICT
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS feedback_status_log (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    feedback_id INT UNSIGNED NOT NULL,
    changed_by  INT UNSIGNED NOT NULL,
    old_status  VARCHAR(20) NULL,
    new_status  VARCHAR(20) NOT NULL,
    changed_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_statuslog_feedback FOREIGN KEY (feedback_id) REFERENCES feedback(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------
-- Offline/Online sync bookkeeping for Vouchers & Production Orders
-- (the two "core modules" that need to keep working without
-- internet: voucher entry at the accounts desk, and production
-- completion on the shop floor).
-- ---------------------------------------------------------------
CREATE TABLE IF NOT EXISTS sync_log (
    id                 INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    client_uuid        VARCHAR(64) NOT NULL UNIQUE,
    device_id          VARCHAR(100) NULL,
    action_type        ENUM('voucher_create','production_complete') NOT NULL,
    record_id          INT UNSIGNED NULL COMMENT 'voucher_id or production_order id once applied',
    payload            JSON NOT NULL,
    status             ENUM('applied','failed','duplicate') NOT NULL DEFAULT 'applied',
    error_message      VARCHAR(500) NULL,
    client_created_at  DATETIME NOT NULL,
    synced_at          TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Traceability columns so the UI can badge "Pending Sync" / "Synced".
DELIMITER $$
CREATE PROCEDURE add_sync_columns_if_missing()
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'vouchers' AND COLUMN_NAME = 'client_uuid'
    ) THEN
        ALTER TABLE vouchers
            ADD COLUMN client_uuid VARCHAR(64) NULL UNIQUE AFTER id,
            ADD COLUMN sync_status ENUM('synced','pending','conflict') NOT NULL DEFAULT 'synced';
    END IF;

    IF NOT EXISTS (
        SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'production_orders' AND COLUMN_NAME = 'client_uuid'
    ) THEN
        ALTER TABLE production_orders
            ADD COLUMN client_uuid VARCHAR(64) NULL UNIQUE AFTER id,
            ADD COLUMN sync_status ENUM('synced','pending','conflict') NOT NULL DEFAULT 'synced';
    END IF;
END$$
DELIMITER ;

CALL add_sync_columns_if_missing();
DROP PROCEDURE add_sync_columns_if_missing;