-- Company Settings table
-- Run against your ERP database (e.g. erp_foundation)
-- Safe to re-run: uses IF NOT EXISTS and checks for default row

CREATE TABLE IF NOT EXISTS `company_settings` (
    `id`                        TINYINT UNSIGNED    NOT NULL AUTO_INCREMENT,
    `name`                      VARCHAR(200)        NOT NULL,
    `legal_name`                VARCHAR(200)        NULL,
    `logo_path`                 VARCHAR(255)        NULL,
    `currency_code`             CHAR(3)             NOT NULL DEFAULT 'BHD',
    `currency_symbol`           VARCHAR(10)         NOT NULL DEFAULT 'BD',
    `decimal_places`            TINYINT UNSIGNED    NOT NULL DEFAULT 3,
    `address`                   TEXT                NULL,
    `city`                      VARCHAR(100)        NULL,
    `country`                   VARCHAR(100)        NULL,
    `postal_code`               VARCHAR(20)         NULL,
    `phone`                     VARCHAR(30)         NULL,
    `email`                     VARCHAR(191)        NULL,
    `website`                   VARCHAR(255)        NULL,
    `tax_id`                    VARCHAR(50)         NULL,
    `commercial_registration`   VARCHAR(50)         NULL,
    `timezone`                  VARCHAR(50)         NOT NULL DEFAULT 'Asia/Bahrain',
    `locale`                    VARCHAR(10)         NOT NULL DEFAULT 'en_BH',
    `date_format`               VARCHAR(20)         NOT NULL DEFAULT 'd/m/Y',
    `fiscal_year_start_month`   TINYINT UNSIGNED    NOT NULL DEFAULT 1,
    `created_at`                DATETIME            NULL,
    `updated_at`                DATETIME            NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Default company row (singleton — only one row should exist)
INSERT INTO `company_settings` (
    `name`,
    `legal_name`,
    `currency_code`,
    `currency_symbol`,
    `decimal_places`,
    `country`,
    `timezone`,
    `locale`,
    `date_format`,
    `fiscal_year_start_month`,
    `created_at`,
    `updated_at`
)
SELECT
    'My Company',
    'My Company W.L.L.',
    'BHD',
    'BD',
    3,
    'Bahrain',
    'Asia/Bahrain',
    'en_BH',
    'd/m/Y',
    1,
    NOW(),
    NOW()
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM `company_settings` LIMIT 1);
