-- Slice 8V-CompanyProfile · schema
-- Single-row "our company" profile. Field-level RBAC lives in the model, not the schema.
--   Contact block   → CRM editable (contact info, socials, logo)
--   Legal / Tax     → Executive editable (legal name, TIN, VAT, reg number)
--   Bank            → Executive editable
--   Everything      → Administrator editable
-- Read: any authenticated role that has PricingDefaults or CompanyProfile perm.

CREATE TABLE IF NOT EXISTS company_profile (
    c0d               INT UNSIGNED   NOT NULL AUTO_INCREMENT,

    -- Trading identity (CRM editable)
    trading_name      VARCHAR(200)   NOT NULL DEFAULT 'DS Pera',
    short_name        VARCHAR(60)    NOT NULL DEFAULT 'DSP',
    logo_path         VARCHAR(500)   NULL,
    tagline           VARCHAR(200)   NULL,

    -- Contact (CRM editable)
    physical_address  VARCHAR(300)   NULL,
    postal_address    VARCHAR(200)   NULL,
    region            VARCHAR(80)    NULL,
    district          VARCHAR(120)   NULL,
    phone_primary     VARCHAR(40)    NULL,
    phone_secondary   VARCHAR(40)    NULL,
    email_primary     VARCHAR(120)   NULL,
    email_secondary   VARCHAR(120)   NULL,
    website           VARCHAR(200)   NULL,

    -- Legal / Tax (Executive editable)
    legal_name        VARCHAR(200)   NOT NULL DEFAULT 'DS Pera Ltd',
    reg_number        VARCHAR(80)    NULL,
    tin               VARCHAR(40)    NULL,
    vat_number        VARCHAR(40)    NULL,
    tax_regime        VARCHAR(80)    NULL,

    -- Bank (Executive editable)
    bank_name         VARCHAR(120)   NULL,
    bank_branch       VARCHAR(120)   NULL,
    bank_account_name VARCHAR(200)   NULL,
    bank_account_no   VARCHAR(60)    NULL,
    bank_swift        VARCHAR(20)    NULL,
    bank_currency     VARCHAR(6)     NOT NULL DEFAULT 'TZS',

    -- Meta
    edited_by         INT UNSIGNED   NULL,
    edited_by_name    VARCHAR(160)   NULL,
    edited_on         TIMESTAMP      NULL,

    PRIMARY KEY (c0d)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO company_profile (c0d, trading_name, short_name, legal_name, bank_currency)
VALUES (1, 'DS Pera', 'DSP', 'DS Pera Ltd', 'TZS')
ON DUPLICATE KEY UPDATE c0d = c0d;
