-- Schéma consolidé (état final après les 20 migrations de src/DevisFactures.Data/SchemaScripts.cs,
-- MySqlVersion1..20) — pas de rejeu migration par migration, la base cPanel est neuve. À exécuter une seule
-- fois via phpMyAdmin sur la base vide créée dans cPanel.

CREATE TABLE IF NOT EXISTS SchemaInfo (
    Version INT NOT NULL,
    AppliedAtUtc VARCHAR(40) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS AppUsers (
    Id CHAR(36) PRIMARY KEY,
    UserName VARCHAR(100) NOT NULL UNIQUE,
    PasswordHash VARCHAR(200) NOT NULL,
    PasswordSalt VARCHAR(200) NOT NULL,
    PasswordIterations INT NOT NULL,
    Role INT NOT NULL,
    IsActive TINYINT(1) NOT NULL DEFAULT 1,
    CreatedAtUtc VARCHAR(40) NOT NULL,
    UpdatedAtUtc VARCHAR(40) NULL,
    LastLoginAtUtc VARCHAR(40) NULL,
    ThemePreference INT NOT NULL DEFAULT 1
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS AuditLogEntries (
    Id CHAR(36) PRIMARY KEY,
    UserId CHAR(36) NOT NULL,
    UserDisplayName VARCHAR(200) NOT NULL,
    ActionType INT NOT NULL,
    EntityType VARCHAR(100) NOT NULL,
    EntityId CHAR(36) NULL,
    Details TEXT NOT NULL,
    OccurredAtUtc VARCHAR(40) NOT NULL,
    INDEX IX_AuditLogEntries_OccurredAtUtc (OccurredAtUtc)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS CompanySettings (
    Id CHAR(36) PRIMARY KEY,
    Name VARCHAR(200) NOT NULL,
    Address1 VARCHAR(300) NOT NULL,
    Address2 VARCHAR(300) NOT NULL,
    Phone1 VARCHAR(50) NOT NULL,
    Phone2 VARCHAR(50) NOT NULL,
    Email VARCHAR(200) NOT NULL,
    Siret VARCHAR(20) NOT NULL,
    LogoPath VARCHAR(500) NOT NULL,
    LegalFooterText TEXT NOT NULL,
    DefaultPaymentTermsText TEXT NOT NULL,
    QuoteNumberFormat VARCHAR(100) NOT NULL,
    InvoiceNumberFormat VARCHAR(100) NOT NULL,
    UpdatedAtUtc VARCHAR(40) NULL,
    DefaultVatRate DECIMAL(6,2) NOT NULL DEFAULT 20,
    RibPath VARCHAR(500) NOT NULL DEFAULT '',
    SalesAccountNumber VARCHAR(20) NOT NULL DEFAULT '706000',
    ClientAccountNumber VARCHAR(20) NOT NULL DEFAULT '411000',
    VatAccountNumber VARCHAR(20) NOT NULL DEFAULT '445710',
    IntakeConditionsText TEXT NOT NULL DEFAULT ''
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS EmailSettings (
    Id CHAR(36) PRIMARY KEY,
    SmtpHost VARCHAR(200) NOT NULL,
    SmtpPort INT NOT NULL,
    UseSsl TINYINT(1) NOT NULL,
    Username VARCHAR(200) NOT NULL,
    EncryptedPassword VARCHAR(1000) NOT NULL,
    FromAddress VARCHAR(200) NOT NULL,
    FromDisplayName VARCHAR(200) NOT NULL,
    ThunderbirdExecutablePath VARCHAR(500) NOT NULL,
    QuoteSubjectTemplate VARCHAR(300) NOT NULL,
    QuoteBodyTemplate TEXT NOT NULL,
    InvoiceSubjectTemplate VARCHAR(300) NOT NULL,
    InvoiceBodyTemplate TEXT NOT NULL,
    UpdatedAtUtc VARCHAR(40) NULL,
    Signature TEXT NOT NULL DEFAULT '',
    ReminderSubjectTemplate TEXT NOT NULL DEFAULT '',
    ReminderBodyTemplate TEXT NOT NULL DEFAULT ''
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS Establishments (
    Id CHAR(36) PRIMARY KEY,
    Name VARCHAR(200) NOT NULL,
    CreatedAtUtc VARCHAR(40) NOT NULL,
    UpdatedAtUtc VARCHAR(40) NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS AcquisitionSources (
    Id CHAR(36) PRIMARY KEY,
    Name VARCHAR(200) NOT NULL,
    CreatedAtUtc VARCHAR(40) NOT NULL,
    UpdatedAtUtc VARCHAR(40) NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS Clients (
    Id CHAR(36) PRIMARY KEY,
    Type INT NOT NULL,
    DisplayName VARCHAR(300) NOT NULL,
    Address VARCHAR(300) NOT NULL,
    PostalCode VARCHAR(20) NOT NULL,
    City VARCHAR(150) NOT NULL,
    Phone VARCHAR(50) NOT NULL,
    Mobile VARCHAR(50) NOT NULL,
    Email VARCHAR(200) NOT NULL,
    Notes TEXT NOT NULL,
    IsActive TINYINT(1) NOT NULL DEFAULT 1,
    CreatedAtUtc VARCHAR(40) NOT NULL,
    UpdatedAtUtc VARCHAR(40) NULL,
    Siret VARCHAR(20) NOT NULL DEFAULT '',
    AcquisitionSourceId CHAR(36) NULL,
    INDEX IX_Clients_DisplayName (DisplayName),
    INDEX IX_Clients_City (City),
    CONSTRAINT FK_Clients_AcquisitionSources FOREIGN KEY (AcquisitionSourceId) REFERENCES AcquisitionSources(Id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS ArticleCategories (
    Id CHAR(36) PRIMARY KEY,
    Name VARCHAR(200) NOT NULL UNIQUE,
    IsActive TINYINT(1) NOT NULL DEFAULT 1,
    CreatedAtUtc VARCHAR(40) NOT NULL,
    UpdatedAtUtc VARCHAR(40) NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS Articles (
    Id CHAR(36) PRIMARY KEY,
    CategoryId CHAR(36) NULL,
    Reference VARCHAR(100) NOT NULL,
    Designation VARCHAR(400) NOT NULL,
    DefaultUnitPriceHt DECIMAL(14,4) NOT NULL,
    DefaultVatRate DECIMAL(6,2) NOT NULL,
    IsActive TINYINT(1) NOT NULL DEFAULT 1,
    CreatedAtUtc VARCHAR(40) NOT NULL,
    UpdatedAtUtc VARCHAR(40) NULL,
    INDEX IX_Articles_Designation (Designation),
    INDEX IX_Articles_CategoryId (CategoryId),
    CONSTRAINT FK_Articles_ArticleCategories FOREIGN KEY (CategoryId) REFERENCES ArticleCategories(Id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS Documents (
    Id CHAR(36) PRIMARY KEY,
    DocumentType INT NOT NULL,
    Number VARCHAR(50) NULL UNIQUE,
    ClientId CHAR(36) NOT NULL,
    IssueDate VARCHAR(10) NOT NULL,
    Status INT NOT NULL,
    Notes TEXT NOT NULL,
    PaymentTermsText TEXT NOT NULL,
    ConvertedFromDocumentId CHAR(36) NULL,
    CreatedByUserId CHAR(36) NOT NULL,
    CreatedAtUtc VARCHAR(40) NOT NULL,
    UpdatedAtUtc VARCHAR(40) NULL,
    EstablishmentId CHAR(36) NULL,
    Activity INT NULL,
    IsCreditNote TINYINT(1) NOT NULL DEFAULT 0,
    INDEX IX_Documents_ClientId (ClientId),
    INDEX IX_Documents_DocumentType (DocumentType),
    CONSTRAINT FK_Documents_Clients FOREIGN KEY (ClientId) REFERENCES Clients(Id),
    CONSTRAINT FK_Documents_AppUsers FOREIGN KEY (CreatedByUserId) REFERENCES AppUsers(Id),
    CONSTRAINT FK_Documents_Establishments FOREIGN KEY (EstablishmentId) REFERENCES Establishments(Id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS DocumentLines (
    Id CHAR(36) PRIMARY KEY,
    DocumentId CHAR(36) NOT NULL,
    ArticleId CHAR(36) NULL,
    Designation VARCHAR(400) NOT NULL,
    DetailText TEXT NOT NULL,
    Quantity DECIMAL(14,4) NOT NULL,
    UnitPriceHt DECIMAL(14,4) NOT NULL,
    DiscountPercent DECIMAL(6,2) NOT NULL,
    VatRate DECIMAL(6,2) NOT NULL,
    SortOrder INT NOT NULL,
    IsFreeText TINYINT(1) NOT NULL DEFAULT 0,
    INDEX IX_DocumentLines_DocumentId (DocumentId),
    CONSTRAINT FK_DocumentLines_Documents FOREIGN KEY (DocumentId) REFERENCES Documents(Id),
    CONSTRAINT FK_DocumentLines_Articles FOREIGN KEY (ArticleId) REFERENCES Articles(Id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS DocumentCounters (
    DocumentType INT PRIMARY KEY,
    LastSequence BIGINT NOT NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS Licenses (
    Id CHAR(36) PRIMARY KEY,
    LicenseKey VARCHAR(200) NOT NULL UNIQUE,
    ClientId CHAR(36) NULL,
    AssignedAtUtc VARCHAR(40) NULL,
    CreatedAtUtc VARCHAR(40) NOT NULL,
    UpdatedAtUtc VARCHAR(40) NULL,
    CONSTRAINT FK_Licenses_Clients FOREIGN KEY (ClientId) REFERENCES Clients(Id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS Payments (
    Id CHAR(36) PRIMARY KEY,
    DocumentId CHAR(36) NOT NULL,
    Method INT NOT NULL,
    Amount DECIMAL(14,4) NOT NULL,
    PaymentDate VARCHAR(10) NOT NULL,
    Notes TEXT NOT NULL,
    CreatedByUserId CHAR(36) NOT NULL,
    CreatedAtUtc VARCHAR(40) NOT NULL,
    UpdatedAtUtc VARCHAR(40) NULL,
    CreditedAtBankDate VARCHAR(20) NULL,
    INDEX IX_Payments_DocumentId (DocumentId),
    CONSTRAINT FK_Payments_Documents FOREIGN KEY (DocumentId) REFERENCES Documents(Id),
    CONSTRAINT FK_Payments_AppUsers FOREIGN KEY (CreatedByUserId) REFERENCES AppUsers(Id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS PaymentReminders (
    Id CHAR(36) PRIMARY KEY,
    DocumentId CHAR(36) NOT NULL,
    SentByUserId CHAR(36) NOT NULL,
    CreatedAtUtc VARCHAR(40) NOT NULL,
    UpdatedAtUtc VARCHAR(40) NULL,
    INDEX IX_PaymentReminders_DocumentId (DocumentId),
    CONSTRAINT FK_PaymentReminders_Documents FOREIGN KEY (DocumentId) REFERENCES Documents(Id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS Reminders (
    Id CHAR(36) PRIMARY KEY,
    Title VARCHAR(200) NOT NULL,
    Note TEXT NOT NULL,
    DueDate DATE NOT NULL,
    RecurrenceMonths INT NULL,
    DocumentId CHAR(36) NULL,
    CreatedByUserId CHAR(36) NOT NULL,
    IsDone TINYINT(1) NOT NULL DEFAULT 0,
    CompletedAtUtc VARCHAR(40) NULL,
    CreatedAtUtc VARCHAR(40) NOT NULL,
    UpdatedAtUtc VARCHAR(40) NULL,
    PaymentId CHAR(36) NULL,
    INDEX IX_Reminders_DueDate (DueDate),
    INDEX IX_Reminders_DocumentId (DocumentId),
    INDEX IX_Reminders_PaymentId (PaymentId),
    CONSTRAINT FK_Reminders_Documents FOREIGN KEY (DocumentId) REFERENCES Documents(Id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS FreeTextSnippets (
    Id CHAR(36) PRIMARY KEY,
    Title VARCHAR(200) NOT NULL,
    Text TEXT NOT NULL,
    CreatedAtUtc VARCHAR(40) NOT NULL,
    UpdatedAtUtc VARCHAR(40) NULL
) ENGINE=InnoDB;

INSERT INTO SchemaInfo (Version, AppliedAtUtc) SELECT 20, DATE_FORMAT(UTC_TIMESTAMP(4), '%Y-%m-%dT%H:%i:%s.%fZ') FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM SchemaInfo WHERE Version = 20);